0

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?

New contributor
Nu Ta is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 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 at 18:17
  • The keys are not in the table, they just seperate the rows. – Nu Ta May 22 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 at 18:30
  • It should just show as an array inside the specific field. – Nu Ta May 22 at 18:45
0

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

| improve this answer | |
  • Works like a charm, thank you! – Nu Ta May 22 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 at 21:02

Your Answer

Nu Ta is a new contributor. Be nice, and check out our Code of Conduct.

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.