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

"key1": {
          "value1": "normal",
          "value2": [
"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:

 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?

  • What do you want to do with the array for value2? Where should the elements there go? or does that always contains exactly one element? – a_horse_with_no_name May 22 '20 at 18:17
  • The keys are not in the table, they just seperate the rows. – Nu Ta May 22 '20 at 18:29
  • I am talking about the "PogU" value, what do you want to do with that array? – a_horse_with_no_name May 22 '20 at 18:30
  • It should just show as an array inside the specific field. – Nu Ta May 22 '20 at 18:45

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

  • Works like a charm, thank you! – Nu Ta May 22 '20 at 18:46
  • @NuTa If that answer solved your question then please accept it, so that your question is marked as resolved. – a_horse_with_no_name May 22 '20 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.