How to import a JSON file into postgresql databse?

Question

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

COPY can't "read" json, just INSERT a single row into a single column. — Frank Heikens
– Frank Heikens, Commented Feb 10, 2021 at 15:21
Don't have time for a full answer. See JSON functions. As example: select * from json_to_record('{"s":"S1","p":"P1","j":"J1","qty":200}') as t(s varchar, p varchar, j varchar, qty integer); s | p | j | qty ----+----+----+----- S1 | P1 | J1 | 200 — Adrian Klaver
– Adrian Klaver, Commented Feb 10, 2021 at 16:32

Jim Jones · Accepted Answer · 2021-02-11 11:21:10Z

4

You can import this json file of yours to a temporary table and from there populate the table notifies. For example:

Create a tmp table ..

CREATE TABLE tmp (c text);

.. import your json file into the table tmp using COPY ..

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

... and finally populate the table 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)

After that you may want to drop the table tmp

DROP TABLE tmp;

EDIT: A quite elegant alternative is to use json_populate_record, as suggested by @Jeremy. Thanks! See comments below.

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)

edited Feb 11, 2021 at 11:21

answered Feb 10, 2021 at 17:30

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremy Over a year ago

Note that instead of json_to_record(c::json) AS q (s text, p text, j text, qty int), you could use json_populate_record(null::notifies, c::json). It's easier to reuse the notifies type.

Fritz Larco · Accepted Answer · 2022-12-19 12:36:11Z

One way to this is to use sling. See this blog post which covers loading JSON files into PG.

# in Windows Powershell
$ set POSTGRES 'postgresql://...'

$ sling conns list
+------------+------------------+-----------------+
| CONN NAME  | CONN TYPE        | SOURCE          |
+------------+------------------+-----------------+
| POSTGRES   | DB - PostgreSQL  | env variable    |
+------------+------------------+-----------------+

$ sling run --src-stream file://C:/temp/records.json --tgt-conn POSTGRES --tgt-object public.records --mode full-refresh
11:09AM INF connecting to target database (postgres)
11:09AM INF reading from source file system (file)
11:09AM INF writing to target database [mode: full-refresh]
11:09AM INF streaming data
11:09AM INF dropped table public.records
11:09AM INF created table public.records
11:09AM INF inserted 500 rows in 0 secs [1,556 r/s]
11:09AM INF execution succeeded

Using debug mode would show a DDL of create table if not exists public.records ("data" jsonb). If you would like to flatten your JSON, sling does that as well by adding the --src-options 'flatten: true' option:

$ sling run --src-stream file://C:/temp/records.json --src-options 'flatten: true' --tgt-conn POSTGRES --tgt-object public.records --mode full-refresh

The DDL in that case would be something like:

create table if not exists public.records ("_id" varchar(255),
"age" integer,
"balance" varchar(255),
"company__about" text,
"company__address" varchar(255),
"company__email" varchar(255),
"company__latitude" numeric,
"company__longitude" numeric,
"company__name" varchar(255),
"company__phone" varchar(255),
"company__registered" varchar(255),
"isactive" bool,
"name" varchar(255),
"picture" varchar(255),
"tags" jsonb)

FYI, I am the author of sling.

Collectives™ on Stack Overflow

How to import a JSON file into postgresql databse?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related