2

I'm trying to insert a JSON array that came from another JSON array (array of arrays) into a table using json_populate_recordset(), but I'm receiving the following error:

SQL Error [22023]: ERROR: argument of json_populate_recordset must be an array of objects

Here's an example of what I'm trying to do:

DO $$
DECLARE 
    j json;
BEGIN
    j := '[[{"a":1,"b":1.23},{"a":2,"b":2.34}],
           [{"a":2,"b":1.23},{"a":3,"b":2.34}],
           [{"a":3,"b":1.23},{"a":4,"b":2.34}]]'::json;

    CREATE TEMPORARY TABLE testj (j json);

    INSERT INTO testj
    SELECT * FROM json_populate_recordset(null::testj, j);

    DROP TABLE testj;
END
$$;

The final table should be something like this:

                 j                  |
------------------------------------|
[{"a":1,"b":1.23},{"a":2,"b":2.34}] |
[{"a":1,"b":1.23},{"a":2,"b":2.34}] |
[{"a":1,"b":1.23},{"a":2,"b":2.34}] |

I'm using a PostgreSQL 9.5 database. What am I doing wrong?

  • 1
    Your table has only one column of json type where json_populate_recordset is aimed at "opening" the JSON structure and put it into a table with the matching columns as keys. So this is probably not the right tool for you. See the unnest function instead. – Patrick Mevzek Dec 14 '17 at 20:13
  • See: SELECT unnest(ARRAY['[{"a":1,"b":1.23},{"a":2,"b":2.34}]'::json, '[{"a":2,"b":1.23},{"a":3,"b":2.34}]'::json, '[{"a":3,"b":1.23},{"a":4,"b":2.34}]'::json]) ; – Patrick Mevzek Dec 14 '17 at 20:16
0

Use the function json_array_elements() instead, available in Postgres 9.5.
Quoting the manual, it ...

Expands a JSON array to a set of JSON values.

And values can in turn be JSON arrays (or any other JSON values).

INSERT INTO testj (j)
SELECT * FROM json_array_elements(j);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.