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?

4
  • 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?
    – user1822
    Commented May 22, 2020 at 18:17
  • The keys are not in the table, they just seperate the rows. Commented May 22, 2020 at 18:29
  • I am talking about the "PogU" value, what do you want to do with that array?
    – user1822
    Commented May 22, 2020 at 18:30
  • It should just show as an array inside the specific field. Commented May 22, 2020 at 18:45

1 Answer 1

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

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

Your Answer

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

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