How to import values of a JSON to PostgreSQL?

Question

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?

Answer 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