小编典典
您可以将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;
2020-07-27