如何将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？

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

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

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

然后，您可以使用上面的语句，只需删除CTE并使用登台表：

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;