How can I import a JSON file into PostgreSQL?

For example I have a file customers.json which is an array of objects (strictly formed) and it’s pretty plain (without nested objects) like this (what is important: it’s already include ids):

[
  {
    "id": 23635,
    "name": "Jerry Green",
    "comment": "Imported from facebook."
  },
  {
    "id": 23636,
    "name": "John Wayne",
    "comment": "Imported from facebook."
  }
]

[

  {

    "id": 23635,

    "name": "Jerry Green",

    "comment": "Imported from facebook."

  },

  {

    "id": 23636,

    "name": "John Wayne",

    "comment": "Imported from facebook."

  }

]

​

And I want to import them all into my postgres db into a table customers.

I found some pretty difficult ways when I should import it as json-typed column to a table like imported_json and column named data with objects listed there, then to use sql to get these values and insert it into a real table.

But is there a simple way of importing json to postgres with no touching of sql?

Answer

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;

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

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

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

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;

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;

​