json - 将 JSON 文件作为一行复制到 PostgreSQL 中的表中的问题
问题描述
我有一个简单的 JSON 文档“example_1.JSON:
{
"fruit": "Apple",
"size": "Large",
"color": "Red"
}
我创建了一个临时表“temp_json”来将文件复制到其中:
CREATE TEMP TABLE temp_json (mydata text);
我使用以下语句复制了 JSON 文件:
COPY temp_json from 'C:\Program Files\PostgreSQL\9.5\data\example_1.JSON';
复制部分没问题。当我将临时表中的值插入到我的数据库表“jsontable”中时,插入没有错误,但它在我的数据库表中的几行中插入了 JSON 值!!!我的数据库表创建如下:
CREATE TABLE public.jsontable (
id bigint NOT NULL DEFAULT nextval('jsontable_id_seq'::regclass),
jsondata jsonb,
CONSTRAINT jsontable_pkey PRIMARY KEY (id)
);
从 temp 表到 jsontable 的插入语句:
INSERT INTO jsontable(jsondata) SELECT to_jsonb(mydata::text) FROM temp_json;
但是当我从 jsontable 中选择行时,我没有在一行中获得 JSON 值!
SELECT * FROM jsontable;
有什么建议可以解决这个问题吗?
解决方案
你有两个选择。控制源文件中的数据,即在复制前预先格式化文件内容以将它们放在一行中。
另一种选择可能是使用string_agg
. 为了保持顺序,我建议您在临时表中有一个默认标识列。
create sequence seq_temp_json;
CREATE temp TABLE temp_json
(
id INT DEFAULT NEXTVAL('seq_temp_json'::regclass),
mydata TEXT
);
现在,加载临时表并检查顺序,json顺序应该是id的升序。
COPY temp_json(mydata) from 'C:\Program Files\PostgreSQL\9.5\data\example_1.JSON';
knayak=# select * from temp_json;
id | mydata
----+-------------------
1 | {
2 | "fruit": "Apple",
3 | "size": "Large",
4 | "color": "Red"
5 | }
(5 rows)
将 JSON 加载到主表中
INSERT INTO jsontable ( jsondata )
SELECT string_agg( mydata ,e'\n' ORDER BY id)::jsonb
FROM temp_json;
该列现在包含完整的 JSONB。
knayak=# select * from jsontable;
id | jsondata
----+-----------------------------------------------------
6 | {"size": "Large", "color": "Red", "fruit": "Apple"}
(1 row)