如何将 JSON 文件导入 PostgreSQL? [英] How can I import a JSON file into PostgreSQL?

问题描述

例如我有一个文件 customers.json 它是一个对象数组(严格形成)，它非常简单(没有嵌套对象)像这样(重要的是:它已经包含 id):

<预><代码>[{身份证":23635，"name": "杰瑞格林","comment": "从脸书导入."},{身份证":23636，"name": "约翰·韦恩","comment": "从脸书导入."}]

我想将它们全部导入到我的 postgres 数据库中，并将它们导入一个表 customers.

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

但是有没有一种简单的方法可以在不涉及 sql 的情况下将 json 导入 postgres?

解决方案

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

with customer_json (doc) as (价值观('[{身份证":23635，"name": "杰瑞格林","comment": "从脸书导入."},{身份证":23636，"name": "约翰·韦恩","comment": "从脸书导入."}]'::json))插入客户(ID、姓名、评论)选择 p.*来自 customer_json l交叉连接横向 json_populate_recordset(null::customer, doc) as p在冲突(id)上做更新设置名称 = 排除.名称，评论 = 排除.评论；

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

<小时>

以上假设表定义如下:

创建表客户(id 整数主键，名称文本不为空，评论文字);

<小时>

如果数据以文件形式提供，您需要先将该文件放入数据库中的某个表中.像这样:

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)选择 p.*从 customer_import l交叉连接横向 json_populate_recordset(null::customer, doc) as p在冲突(id)上做更新设置名称 = 排除.名称，评论 = 排除.评论；

For example I have a file customers.json which is an array of objects (strictly formed) and it's pretty plain (without nested objects) like this (what is important: it's already include ids):

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

And I want to import them all into my postgres db into a table customers.

I found some pretty difficult ways when I should import it as json-typed column to a table like imported_json and column named data with objects listed there, then to use sql to get these values and insert it into a real table.

But is there a simple way of importing json to postgres with no touching of sql?

解决方案

You can feed the JSON into a SQL statement that extracts the information and inserts that into the table. If the JSON attributes have exactly the name as the table columns you can do something like this:

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;

New customers will be inserted, existing ones will be updated. The "magic" part is the json_populate_recordset(null::customer, doc) which generates a relational representation of the JSON objects.

---

The above assumes a table definition like this:

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

---

If the data is provided as a file, you need to first put that file into some table in the database. Something like this:

create unlogged table customer_import (doc json);

Then upload the file into a single row of that table, e.g. using the copy command in psql (or whatever your SQL client offers):

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

Then you can use the above statement, just remove the CTE and use the 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;

这篇关于如何将 JSON 文件导入 PostgreSQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！