86

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."
  }
]

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?

Improve this question
3
  • 1
    "with no touching of sql" no. The only way to interact with a relational database that speaks SQL is - well - SQL. Commented Aug 30, 2016 at 9:59
  • @a_horse_with_no_name oh... I pretty sure then I can simply convert my json to sql query. I will research this :) Commented Aug 30, 2016 at 10:04
  • @a_horse_with_no_name Not true. You can use a library that wraps SQL. In Ruby we have ActiveRecord and Sequel. Commented Feb 14, 2020 at 9:49

7 Answers 7

Reset to default
89

It turns out there's an easy way to import a multi-line JSON object into a JSON column in a postgres database using the command line psql tool, without needing to explicitly embed the JSON into the SQL statement. The technique is documented in the postgresql docs, but it's a bit hidden.

The trick is to load the JSON into a psql variable using backticks. For example, given a multi-line JSON file in /tmp/test.json such as:

{
  "dog": "cat",
  "frog": "frat"
}

We can use the following SQL to load it into a temporary table:

sql> \set content `cat /tmp/test.json`
sql> create temp table t ( j jsonb );
sql> insert into t values (:'content');
sql> select * from t;

which gives the result:

               j                
────────────────────────────────
 {"dog": "cat", "frog": "frat"}
(1 row)

You can also perform operations on the data directly:

sql> select :'content'::jsonb -> 'dog';
 ?column? 
──────────
 "cat"
(1 row)

Under the covers this is just embedding the JSON in the SQL, but it's a lot neater to let psql perform the interpolation itself.

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

8 Comments

^ this is gold. Just imported 26000 records into my db in less than 5 minutes. Not a single step took more than a second.
how do I add an index column to the table by extracting a specific field from each json dict?
I think this will answer your question: stackoverflow.com/questions/17807030/…
Great feature. 'type' is the 'cat' equivalent in Windows.
@DoctorEval This is incredibly useful. Could you do the same thing from a plpgsql function?
|
85

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

You may have to make it one line:

COPY asn_temp FROM PROGRAM 'curl https://raw.githubusercontent.com/ipverse/asn-ip/master/as/1234/aggregated.json | jq -rc "."'

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;

Another example to insert mutiple rows by unpacking an array

INSERT INTO asn_ips (asn, ip) 
    SELECT * FROM (
        -- get the 'asn' key
        SELECT json_extract_path_text(data, 'asn')::int as asn,
        -- get the 'subnets'.'ipv6' sub key and remove quotes with json_array_elements_text
         json_array_elements_text(json_extract_path_text(data, 'subnets', 'ipv6')::json)::cidr as ip
        FROM public.asn_temp
    UNION
        -- get the 'asn' key
        SELECT json_extract_path_text(data, 'asn')::int as asn,
        -- get the 'subnets'.'ipv4' sub key and remove quotes with json_array_elements_text
         json_array_elements_text(json_extract_path_text(data, 'subnets', 'ipv4')::json)::cidr as ip
        FROM public.asn_temp
) as ips GROUP BY asn, ip;
Improve this answer

4 Comments

I appreciate your help but I found that I better convert my json to sql (with ruby in my case). And then simply import sql with psql command. It's easier than parsing json objects with sql for me :) Btw thx.
can you also help me with this? stackoverflow.com/questions/65602905/…
@a_horse_with_no_name and anyone who needs to import json file. This was just tremendously helpful. Still don't understand how the cross join lateral works... but it works.
what if its a large JSON object?
44

In near big-data cases, the most efficient way to import json from a file, not using any external tool, appear to not import a single json from a file but rather a single column csv: A list of one-line jsons:

data.json.csv:

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

then, under psql:

create table t ( j jsonb )
\copy t from 'd:\path\data.json.csv' csv quote e'\x01' delimiter e'\x02'

One record per json (line) will be added into t table.

"\copy from" import was made for csv, and as such loads data line by line. As a result reading one json per line rather than a single json array to be later splited, will not use any intermediate table and will achieve high throughput.

More of that you will less likely hit the max input line-size limitation that will arise if your input json file is too big.

I would thus first convert your input into a single column csv to then import it using the copy command.

Improve this answer

5 Comments

Thanks this was very small but useful bit information I didn't know
Can confirm, I'm importing close to 30-40GBs of files within 2 mins using this approach. Can read more about it in my SO Q&A here
converting to single line json might not be that easy, even if COPY accept binary you might prefer single line json. In this aim the importing json data blog post referenced from related post (mentionned by @scientific_explorer) might be helpfull, but you might also like the --raw-output export option from jq
What worked for me was adding: csv quote e'\x01' delimiter e'\x02'; from the link that the other comment points to.
For me, putting these two lines in the same line worked! \copy t from 'd:\path\data.json.csv' csv quote e'\x01' delimiter e'\x02';
12

You can use spyql. Running the following command would generate INSERT statements that you can pipe into psql:

$ jq -c .[] customers.json | spyql -Otable=customer "SELECT json->id, json->name, json->comment FROM json TO sql"
INSERT INTO "customer"("id","name","comment") VALUES (23635,'Jerry Green','Imported from facebook.'),(23636,'John Wayne','Imported from facebook.');

jq is used to transform the json array into json lines (1 json object per line) and then spyql takes care of converting json lines into INSERT statements.

To import the data into PostgreSQL:

$ jq -c .[] customers.json | spyql -Otable=customer "SELECT json->id, json->name, json->comment FROM json TO sql" | psql  -U your_user_name -h your_host your_database

Disclaimer: I am the author of spyql.

Improve this answer

3 Comments

Really nice tool. This helps when importing from a .json.gz file without having to decompress it first. The argument -Ochunk_size=50 is useful for making sure that the SQL commands are split in chunks so that they don't take up lots of memory in big files. Thanks for suggesting it.
Brilliant tool, thanks a lot for creating it and for the example here!
I needed to quote the jq filter like so: '.[]' for it to work. 'Got psql error over JSON as single quoted string. 'Could not find support for INSERT on SPyQL docs to get over this. (Maybe INSERT no longer supported?) It looks great for analysis though.
6

Another option is to use sling. See this blog post which covers loading JSON files into PG. You could simply pipe your json file like this:

$ export POSTGRES='postgresql://...'

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

$ cat /tmp/records.json | sling run --tgt-conn POSTGRES --tgt-object public.records --mode full-refresh
11:09AM INF connecting to target database (postgres)
11:09AM INF reading from stream (stdin)
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:

$ cat /tmp/records.json | sling run --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.

Improve this answer

1 Comment

I had already given up looking for a simple EL tool when I found this. Awesome, thanks!
3

If you want to do it from a command line ...

NOTE: This isn't a direct answer to your question, as this will require you to convert your JSON to SQL. You will probably have to deal with JSON 'null' when converting anyway. You could use a view or materialized view to make that problem invisible-ish, though.

Here is a script I've used for importing JSON into PostgreSQL (WSL Ubuntu), which basically requires that you mix psql meta commands and SQL in the same command line. Note use of the somewhat obscure script command, which allocates a pseudo-tty:

$ more update.sh
#!/bin/bash
wget <filename>.json
echo '\set content `cat $(ls -t <redacted>.json.* | head -1)` \\ delete from <table>; insert into <table> values(:'"'content'); refresh materialized view <view>; " | PGPASSWORD=<passwd> psql -h <host> -U <user> -d <database>
$

(Copied from my answer at Shell script to execute pgsql commands in files)

Improve this answer

1 Comment

Thank you! This is amazing! I use this approach to load thousands of JSON files to database. It is fast and easy.
3

Simple but standard way here it is.

  1. Pre-Process: Using jq command (https://jqlang.github.io/jq/) to do pre-process you standard json array files.
jq -c '.[]' your_file.json > your_new_file.json
  1. Create a table: Using command line or UI tools
psql -h localhost -p 5432 postgres -U postgres -c "CREATE TABLE temp (data jsonb);"

if you Postgresql comes with password, export it first export PGPASSWORD=<Your Password>, or just use the prefix of PGPASSWORD=<Your Password> before the above command.

  1. Import by Copy command::
cat your_new_file.json | psql -h localhost -p 5432 postgres -U postgres -c "COPY temp (data) FROM STDIN;"

Done.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.