How to insert nested JSON arrays into a table?

Question

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?

• Patrick Mevzek: 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: 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]) ;

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);