使用json_populate_recordset从单个JSON对象填充多个表格

Question

我已经阅读了相关问题，但是与用户kenthewala不同的是，我想将一组JSON对象存入数据库中。

我的JSON文件如下所示：

{
"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"}]
}

数据库中已经存在相应名称的数据表。

伪代码示例：

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

但我不确定如何实现这一点。

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

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

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

等等。

- Picl

---

最好提供至少一张表的实际定义。CREATE TABLE ...。并且请务必使用您的Postgres版本。 - Erwin Brandstetter

你说得对，谢谢提示！我已经相应地修改了我的帖子。 - Picl

Answer 1

三个步骤：

1. 访问 JSON 对象中的字段，使用 ->。
2. 使用 json_populate_recordset() 从 JSON 记录的数组中创建派生表。
3. 分解行类型以用于 INSERT 命令。

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

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.

使用隐式的JOIN LATERAL。相关链接：

• 在JSON数据类型中查询嵌套记录的组合

- Erwin Brandstetter

---

谢谢，目前我在这里遇到了一个语法错误，但是你引导我朝正确的方向前进！不过我必须承认，我曾希望能够以某种变量的形式提取表名，这样就不必手动编写每个SELECT语句了。 - Picl

@Picl：这也是可能的，但你需要使用动态SQL。我已经发布了许多相关答案。尝试搜索。如果你已经有一个给定的结构，那么我怀疑额外的努力是否值得。至于语法错误：添加的fiddle证明了我的代码是有效的。 - Erwin Brandstetter

问题出在我想太多了^^。我以为你缩写了代码，因为它只是一个例子。因此，我像之前的其他语句一样，在最后一个插入语句周围加上了“z AS (...);”，这显然是错误的方式。由于有许多表格，我可能不总是想同时更新所有表格，所以我想尝试动态方式。感谢您的帮助和提供的链接！ - Picl