SQL 如何将 JSON 文件导入 PostgreSQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39224382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I import a JSON file into PostgreSQL?
提问by Jerry Green
For example I have a file customers.jsonwhich 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):
例如，我有一个文件customers.json，它是一个对象数组（严格形成），它非常简单（没有嵌套对象），就像这样（重要的是：它已经包含 id）：
[
{
"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.
我想将它们全部导入到我的 postgres 数据库中customers。
I found some pretty difficult ways when I should import it as json-typed column to a table like imported_jsonand column named datawith objects listed there, then to use sql to get these values and insert it into a real table.
我发现了一些非常困难的方法，当我应该将它作为 json 类型的列导入到一个表中，imported_json并且列中data列出了对象，然后使用 sql 获取这些值并将其插入到一个真实的表中。
But is there a simple way of importing json to postgres with no touching of sql?
但是有没有一种简单的方法可以在不涉及 sql 的情况下将 json 导入 postgres？
回答by a_horse_with_no_name
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:
您可以将 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;
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.
将插入新客户，更新现有客户。“魔术”部分是json_populate_recordset(null::customer, doc)生成 JSON 对象的关系表示。
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 \copycommand in psql(or whatever your SQL client offers):
然后将文件上传到该表的单行中，例如使用以下\copy命令psql（或您的 SQL 客户端提供的任何命令）：
\copy customer_import from 'customers.json' ....
Then you can use the above statement, just remove the CTE and use the staging table:
然后就可以使用上面的语句了，只需要去掉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;
回答by Doctor Eval
It turns out there's an easy way to import a multi-line JSON object into a JSON column in a postgres database using the command line psql tool, without needing to explicitly embed the JSON into the SQL statement. The technique is documented in the postgresql docs, but it's a bit hidden.
事实证明，有一种简单的方法可以使用命令行 psql 工具将多行 JSON 对象导入 postgres 数据库中的 JSON 列，而无需将 JSON 显式嵌入到 SQL 语句中。该技术记录在postgresql docs 中，但它有点隐藏。
The trick is to load the JSON into a psql variable using backticks. For example, given a multi-line JSON file in /tmp/test.jsonsuch as:
诀窍是使用反引号将 JSON 加载到 psql 变量中。例如，给定/tmp/test.json 中的多行 JSON 文件，例如：
{
"dog": "cat",
"frog": "frat"
}
We can use the following SQL to load it into a temporary table:
我们可以使用以下 SQL 将其加载到临时表中：
sql> \set content `cat /tmp/test.json`
sql> create temp table t ( j jsonb );
sql> insert into t values (:'content');
sql> select * from t;
which gives the result:
这给出了结果：
j
────────────────────────────────
{"dog": "cat", "frog": "frat"}
(1 row)
You can also perform operations on the data directly:
也可以直接对数据进行操作：
sql> select :'content'::jsonb -> 'dog';
?column?
──────────
"cat"
(1 row)
Under the covers this isjust embedding the JSON in the SQL, but it's a lot neater to let psql perform the interpolation itself.
在幕后这是刚刚嵌入SQL中的JSON，但它是一个很大整洁，让PSQL进行插值本身。
回答by user1767316
The simplest way to import json from a file appear to not import a single json from a file but rather a single column csv: A list of one-line jsons:
从文件中导入 json 的最简单方法似乎不是从文件中导入单个 json 而是单列 csv：一行 json 的列表：
data.json:
数据.json：
{"id": 23635,"name": "Jerry Green","comment": "Imported from facebook."}
{"id": 23636,"name": "John Wayne","comment": "Imported from facebook."}
then, under psql:
然后，在 psql 下：
create table t ( j jsonb )
\copy t from 'd:\path\data.json'
One record per json (line) will be added into t table.
每个 json（行）一条记录将被添加到 t 表中。
"\copy from" import was made for csv, and as such loads data line by line. As a result reading one json per line rather than a single json array to be later splited, will not use any intermediate table.
"\copy from" 导入是为 csv 进行的，因此逐行加载数据。因此，每行读取一个 json 而不是稍后拆分的单个 json 数组，将不会使用任何中间表。
More of that you will not hit the max input line-size limitationthat will arise if your input json file is too big.
如果您的输入 json 文件太大，您将不会达到最大输入行大小限制。
I would thus first convert your input into a single column csv to then import it using the copy command.
因此，我会首先将您的输入转换为单列 csv，然后使用复制命令将其导入。
