Postgres expand JSON with unkown keys

Question

I want to expand a specific row in my table which has a json column with the followingstructure:

 id|column1      |
---|-------------|
id1|{nested json}|
id2|{nested json}|

Structure of nested JSON is as follows:

{"random_key_1":{"
               known_key_1":"value",
               "known_key_2":"value"
              },
"random_key_2":{"
               known_key_1":"value",
               "known_key_2":"value"
              },
...

I want expand a single row into following format:

my_column_name |known_key_1 |known_key2|
---------------|-----------|----------|
random_key_1   |  value    |  values  |
random_key_2   |  value    |  values  |

Hi, interesting, not sure if this might be of interest stackoverflow.com/questions/25978846/… — IronMan
– IronMan, Commented Sep 29, 2020 at 1:19
Haha! I tired to build my logic based on this post but the nested part is something that postgres doesn't like :( — drew_psy
– drew_psy, Commented Sep 29, 2020 at 1:21

Blue Star · Accepted Answer · 2020-09-29 04:18:03Z

2

You can use the built-in json_each method to expand your outermost JSON into rows without knowing what the keys are. From that point, you can build the known columns directly.

SELECT col1.key AS my_column_name, 
       col1.value->>'known_key_1' AS known_key_1, 
       col1.value->>'known_key_2' AS known_key_2 
FROM main_table, json_each(column1) col1;

 my_column_name | known_key_1 | known_key_2
----------------+-------------+-------------
 random_key_1   | value1      | value2
 random_key_2   | value3      | value4

answered Sep 29, 2020 at 4:18

Blue Star

1,9621 gold badge13 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres expand JSON with unkown keys

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest