如何将 JSON 文件导入 PostgreSQL？

Question

例如，我有一个文件 customers.json 这是一个对象数组（严格格式），它非常简单（没有嵌套对象），就像这样（重要的是：它已经包含 id）：

 [
  {
    "id": 23635,
    "name": "Jerry Green",
    "comment": "Imported from facebook."
  },
  {
    "id": 23636,
    "name": "John Wayne",
    "comment": "Imported from facebook."
  }
]

我想将它们全部导入我的 postgres db 到一个表中 customers 。

当我应该将它作为 json 类型的列导入到 imported_json 和列名为 data 的表中时，我发现了一些非常困难的方法，其中列出了对象，然后使用 sql 获取这些值并将其插入真实表中。

但是有没有一种简单的方法可以将 json 导入 postgres 而无需接触 sql？

原文由 Jerry Green 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可以将 JSON 输入到 SQL 语句中，该语句提取信息并将其插入到表中。如果 JSON 属性的名称与表列的名称完全相同，则可以执行以下操作：

 with customer_json (doc) as (
   values
    ('[
      {
        "id": 23635,
        "name": "Jerry Green",
        "comment": "Imported from facebook."
      },
      {
        "id": 23636,
        "name": "John Wayne",
        "comment": "Imported from facebook."
      }
    ]'::json)
)
insert into customer (id, name, comment)
select p.*
from customer_json l
  cross join lateral json_populate_recordset(null::customer, doc) as p
on conflict (id) do update
  set name = excluded.name,
      comment = excluded.comment;

将插入新客户，更新现有客户。 “神奇”部分是 json_populate_recordset(null::customer, doc) 生成 JSON 对象的关系表示。

---

上面假设了一个像这样的表定义：

 create table customer
(
  id        integer primary key,
  name      text not null,
  comment   text
);

---

如果数据以文件的形式提供，则需要先将该文件放入数据库中的某个表中。像这样的东西：

 create unlogged table customer_import (doc json);

然后将文件上传到该表的单行中，例如使用 --- 中的 psql \copy 命令（或您的 SQL 客户端提供的任何内容）：

 \copy customer_import from 'customers.json' ....

然后就可以使用上面的语句了，只要去掉CTE，使用staging table即可：

 insert into customer (id, name, comment)
select p.*
from customer_import l
  cross join lateral json_populate_recordset(null::customer, doc) as p
on conflict (id) do update
  set name = excluded.name,
      comment = excluded.comment;

原文由 a_horse_with_no_name 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

您可以使用 spyql 。运行以下命令将生成可以通过管道传输到 psql 的 INSERT 语句：

 $ jq -c .[] customers.json | spyql -Otable=customer "SELECT json->id, json->name, json->comment FROM json TO sql"
INSERT INTO "customer"("id","name","comment") VALUES (23635,'Jerry Green','Imported from facebook.'),(23636,'John Wayne','Imported from facebook.');

jq 用于将 json 数组转换为 json 行（每行 1 个 json 对象），然后 spyql 负责将 json 行转换为 INSERT 语句。

要将数据导入 PostgreSQL：

 $ jq -c .[] customers.json | spyql -Otable=customer "SELECT json->id, json->name, json->comment FROM json TO sql" | psql  -U your_user_name -h your_host your_database

免责声明：我是 spyql 的作者。

原文由 Daniel C. Moura 发布，翻译遵循 CC BY-SA 4.0 许可协议