Split SQL Table with fields containing JSON into multiple rows

Question

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!

You are looking for jsonb_each()

user330315
– user330315

2022-02-18 14:38:53 +00:00
Commented Feb 18, 2022 at 14:38 — user330315
– user330315, Commented Feb 18, 2022 at 14:38

Ftisiot · Accepted Answer · 2022-02-18 14:57:19Z

1

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)

answered Feb 18, 2022 at 14:57

Ftisiot

1,8981 gold badge9 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Split SQL Table with fields containing JSON into multiple rows

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

Related