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 $$
    j json;
    j := '[[{"a":1,"b":1.23},{"a":2,"b":2.34}],

    CREATE TEMPORARY TABLE testj (j json);

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

    DROP TABLE testj;

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. Dec 14, 2017 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]) ; Dec 14, 2017 at 20:16

1 Answer 1


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 and acknowledge you have read our privacy policy.

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