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:tempspj.json';

Answer

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:tempspj.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)