Split SQL Table with fields containing JSON into multiple rows-CodePudding

I have a table t with 2 fields: one containing an id, and the other one containing a json.
My table looks like this:

| id | json                                          | 
|:---|:----------------------------------------------|
| 1  | {"tag1":45, "tag2": 3, "tag5": 10}            |
| 2  | {"tag5":35, "tag6": 7, "tag8": 10, "tag10": 4}|
| 3  | {"tag2":10, "tag800": 6}                      |

I am trying to write a postgresql query to create a table that looks like the following but I am stuck:

| id |   key   | Value |
|:---|:--------|:------|
| 1  | tag1    | 45    |
| 1  | tag2    | 3     |
| 1  | tag5    | 10    |
| 2  | tag5    | 35    |
| 2  | tag6    | 7     |
| 2  | tag8    | 10    |
| 2  | tag10   | 4     |
| 3  | tag2    | 10    |
| 3  | tag800  | 6     |

Note that there are thousands of different keys in my data.
Any help would be much appreciated. Thanks!

CodePudding user response：

Recreating your example with

CREATE TABLE test(id int, mydata jsonb);

insert into test values (1, '{"tag1":45, "tag2": 3, "tag5": 10}');
insert into test values (2, '{"tag5":35, "tag6": 7, "tag8": 10, "tag10": 4}');
insert into test values (3, '{"tag2":10, "tag800": 6} ');

You can achieve what you're looking for with the jsonb_each function

select id, key, value from test, jsonb_each(test.mydata)