使用json_populate_recordset从单个JSON对象填充多个表 [英] Populate multiple tables from a single JSON object with json_populate_recordset

问题描述

我已阅读相关问题，但与用户kenthewala不同，我想在数据库中获取一组JSON对象。

I've read related question, but unlike user kenthewala, I want to get an array of JSON objects into the database.

我的JSON文件如下所示：

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.

我正在使用PostgreSQL 9.3.5（如果有帮助的话还有PHP 5.3.3）。

I'm using PostgreSQL 9.3.5 (and PHP 5.3.3 if that helps).

表结构类似于JSON文件（因为我最初从数据库导出JSON）：

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

等等。

推荐答案

3步骤：

1. 访问JSON对象的字段 - > 。


2. 使用 json_populate_recordset（） 。


3. 分解 INSERT 命令的行类型。

1. Access field of JSON object wich ->.
2. Create a derived table from the JSON array of records using json_populate_recordset().
3. Decompose the row-type for the INSERT command.

要重用所有表的输入值，请将其包装在数据修改CTE ：

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小提琴。

SQL Fiddle.

使用隐式 JOIN LATERAL 。相关：

• 使用JSON数据类型的嵌套数组记录查询组合

• Query combinations with nested array of records in JSON datatype

这篇关于使用json_populate_recordset从单个JSON对象填充多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！