How to import values of a JSON to PostgreSQL?

by Nu Ta   Last Updated May 22, 2020 19:06 PM

I have a JSON file gugu.json that looks like this:

{
"key1": {
          "value1": "normal",
          "value2": [
                     "PogU"
                    ]
        },
"key2": ...
}

I want to put all the contents of the JSON file into the table

KEKW(value1 text, value2 text, ...).

I already tried many thing like this:

CREATE TABLE gugu(
 data json
);

\copy gugu FROM 'gugu.json';

SELECT * FROM json_populate_record(null::KEKW, (SELECT data->'key1' FROM gugu));

But as you can see I just don't know how I would get the "second level" of JSON in the table?

Tags : postgresql json


Answers 1


You need to unnest the first level, and the second level in two steps:

Use jsonb_each() instead:

select d.val ->> 'value1' as value1,
       d.val ->> 'value2' as value2
from gugu g
  cross join jsonb_each(g.data) as d(ky,val);

Online example

a_horse_with_no_name
a_horse_with_no_name
May 22, 2020 18:32 PM

Related Questions


Is there a faster way to count JSONB tags?

Updated January 05, 2017 08:02 AM

Postgresql CRUD operation on Single json data row

Updated June 06, 2015 22:02 PM

Postgres multiple columns to json

Updated April 16, 2015 21:02 PM