Postgres expand JSON with unkown keys

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  |

Answer

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