Populate multiple tables from a single JSON object with json_populate_recordset
common-table-expression, import, json, postgresql, sql
Solution
3 Steps:
- Access field of JSON object wich `->`.
- Create a derived table from the JSON array of records using `json_populate_recordset()`.
- Decompose the row-type for the `INSERT` command.
To reuse the input value for all tables, wrap it in data-modifying CTEs:
WITH input AS (
SELECT '{
"tablename_a":[{"a_id":1,"b_id":2,"c_id":3},
{"a_id":2,"b_id":51,"c_id":3}],
"tablename_b":[{"b_id":2,"name":"John Doe", "z_id":123},
{"b_id":51,"name":"Mary Ann", "z_id":412}],
"tablename_c":[{"c_id":3, "OS type":"Windows 7"}],
"tablename_z":[{"z_id":123, "Whatever":"Something"},
{"z_id":123, "Whatever":"Something else"}]
}'::json AS j
)
, a AS (
INSERT INTO tablename_a
SELECT t.*
FROM input i
, json_populate_recordset(NULL::tablename_a, i.j->'tablename_a') t
)
, b AS (
INSERT INTO tablename_b
SELECT t.*
FROM input i
, json_populate_recordset(NULL::tablename_b, i.j->'tablename_b') t
)
-- ... more ...
INSERT INTO tablename_z
SELECT t.*
FROM input i
, json_populate_recordset(NULL::tablename_z, i.j->'tablename_z') t
;
SQL Fiddle.
Using implicit `JOIN LATERAL`. Related:
- Query combinations with nested array of records in JSON datatype
Problem
I've read related question, but unlike user kenthewala, I want to get an array of JSON objects into the database. My JSON file looks like this: ``` { "tablename_a":[{"a_id":1,"b_id":2,"c_id":3}, {"a_id":2,"b_id":51,"c_id":3}], "tablename_b":[{"b_id":2,"name":"John Doe", "z_id":123}, {"b_id":51,"name":"Mary Ann", "z_id":412}], "tablename_c":[{"c_id":3, "OS type":"Windows 7"}], "tablename_z":[{"z_id":123, "Whatever":"Something"}, {"z_id":123, "Whatever":"Something else"}] } ``` Tables with the according names already exist in the db. In pseudo-code I imagined something like ``` for each key in JSON_FILE as tbl_name ( insert into tbl_name select * from json_populate_recordset ( null::tbl_name, 'content of tbl_name' ) ) ``` But I'm not sure, how to realize this. I'm using PostgreSQL 9.3.5 (and PHP 5.3.3 if that helps). The table structure resembles the JSON file (as I exported the JSON originally from the db): ``` create table tablename_a (a_id integer, b_id integer, c_id integer); create table tablename_b (b_id integer, name text, z_id integer); ``` and so on.