How to Import a Json File into Postgresql

How can I import a JSON file into PostgreSQL?

You can feed the JSON into a SQL statement that extracts the information and inserts that into the table. If the JSON attributes have exactly the name as the table columns you can do something like this:

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;

New customers will be inserted, existing ones will be updated. The "magic" part is the json_populate_recordset(null::customer, doc) which generates a relational representation of the JSON objects.

---

The above assumes a table definition like this:

create table customer 
(
  id        integer primary key,
  name      text not null,
  comment   text
);

---

If the data is provided as a file, you need to first put that file into some table in the database. Something like this:

create unlogged table customer_import (doc json);

Then upload the file into a single row of that table, e.g. using the \copy command in psql (or whatever your SQL client offers):

\copy customer_import from 'customers.json' ....

Then you can use the above statement, just remove the CTE and use the staging table:

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;

How to import a JSON file into postgresql databse?

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)

How do import simple jsonl file into Postgres 10 database

If you have to do that only in Postgres create auxiliary schema with go-between tables like this:

create schema jsons;
create table jsons.acoustid_meta(data jsonb);

Copy the file to the go-between table:

copy jsons.acoustid_meta from ...

And parse jsons with the Postgres script:

insert into musicbrainz.acoustid_meta
select id, track, artist, album, album_artist, track_no, disc_no, year
from jsons.acoustid_meta 
cross join jsonb_populate_record(null::musicbrainz.acoustid_meta, data);
truncate jsons.acoustid_meta;

---

Update. You can examine json values by referring to data, example:

insert into musicbrainz.acoustid_meta
select id, track, artist, album, album_artist, track_no, disc_no, year
from jsons.acoustid_meta 
cross join jsonb_populate_record(null::musicbrainz.acoustid_meta, data)
where data->'created' is not null;

How do I COPY IMPORT a json file into postgres?

I ended up using Andre Dunstan's blog and this SO answer which says to format the json in a specific way to use the copy command.

Since my structure is pretty defined for the files I'm parsing, I ended up with the following script.

def file_len(fname):
    # to find the number of lines in the file.
    # Has been pretty efficient even for millions of records
    with open(fname) as f:
        for i, l in enumerate(f):
            pass
    return i + 1

INPUTFILE = '/path/to/input.json'
OUTPUTFILE = '/path/to/output.json.csv'
LEN = file_len(INPUTFILE)
with open(OUTPUTFILE, 'w') as fo:
    with open(INPUTFILE, 'r') as fi:
        for i, l in enumerate(fi):
            # I skip the first line
            if i == 0: continue 
            
            # To remove the ']}}' from the end
            elif i+1 == LEN: _ = fo.write(l[:-3])
            
            # To remove the ',' from the end 
            # and add \n since write does not add newline on its own
            else: _ = fo.write(l[:-2]+'\n') 

# load statement

import sqlalchemy
POSTGRESQL = f'postgresql+psycopg2://{USERNAME}:{PASSWORD}@{HOSTNAME}/{DB}'
engine = sqlalchemy.create_engine(POSTGRESQL, echo=True)
            
con = engine.connect()
trans = con.begin()
LOAD_SQL = f"COPY tablename from '{OUTPUTFILE}' with csv delimiter E'\x01' quote E'\x02' null as '';"
try:
    con.execute(LOAD_SQL)
    trans.commit()
except Exception as e:
    trans.rollback()
finally:
    con.close()

---

Related Topics