时间戳

首页 > 解决方案 > How to import a JSON file into postgresql databse?

sql - How to import a JSON file into postgresql databse?

问题描述

I just tried to read a .JSON file in my PostgreSQL database but it is not able to read it. I did it with a .csv file, but with a .JSON file, I am not able to do it.

This is how the .JSON file looks like:

{"s":"S1","p":"P1","j":"J1","qty":200}
{"s":"S1","p":"P1","j":"J4","qty":700}
{"s":"S2","p":"P3","j":"J1","qty":400}
{"s":"S2","p":"P3","j":"J2","qty":200}

This is the code that I tried, I created the table first and then copy the data from the file into the database.

create table notifies(
    s varchar(999),
    p varchar(999),
    j varchar(999),
    qty varchar(999)
);

copy public.notifies(s,p,j,qty)
from 'C:\temp\spj.json';

标签： sqljsonpostgresql

解决方案

您可以将您的这个 json 文件导入到一个临时表中，然后从那里填充该表notifies。例如：

创建一个 tmp 表..

CREATE TABLE tmp (c text);

tmp..使用COPY ..将您的 json 文件导入表中

mydb=# \copy tmp from 'C:\temp\spj.json'

...最后填充表格notifies：

INSERT INTO notifies 
SELECT q.* FROM tmp, json_to_record(c::json) AS q
 (s text, p text, j text, qty int);

SELECT * FROM notifies;

 s  | p  | j  | qty 
----+----+----+-----
 S1 | P1 | J1 | 200
 S1 | P1 | J4 | 700
 S2 | P3 | J1 | 400
 S2 | P3 | J2 | 200
(4 Zeilen)

之后，您可能想要删除表格tmp

DROP TABLE tmp;

编辑json_populate_record：正如@Jeremy 所建议的，一个非常优雅的替代方案是使用。谢谢！请参阅下面的评论。

INSERT INTO notifies 
SELECT q.* FROM tmp, json_populate_record(null::notifies, c::json) AS q;

SELECT * FROM notifies ;
 s  | p  | j  | qty 
----+----+----+-----
 S1 | P1 | J1 | 200
 S1 | P1 | J4 | 700
 S2 | P3 | J1 | 400
 S2 | P3 | J2 | 200
(4 Zeilen)

推荐阅读