PostgreSQL – How to Insert Nested JSON Arrays into a Table

jsonpostgresqlpostgresql-9.5

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?

Best Answer

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