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

问题描述

例如，我有一个文件customers.json，它是一个对象数组(严格形成)，它很简单(没有嵌套对象)，就像这样(重要的是:它已经包含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."
  }
]

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

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

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

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.

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

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

推荐答案

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

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;

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

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

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

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

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

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屋！