Dynamic json data fields extract into columns in postgres

Question

I want to extract data from json and put into columns.

create table mytable (id integer,data jsonb);

insert into mytable (id,data) values(25,'{"_id":25,"indicator 1":"yes","indicator 2":"yes","_validation_status":{"uid":"validation_status_on_hold","color":"#0000ff","by_whom":"super_admin","label":"On Hold","timestamp":1688643788},"start":"2023-07-03T22:03:30.948+05:30"}');
insert into mytable (id,data) values(26,'{"_id":26,"indicator 2":"no","indicator 1":"yes","_validation_status":{"uid":"validation_status_on_hold","color":"#0000ff","by_whom":"super_admin","label":"On Hold","timestamp":1688643788},"start":"2023-07-03T22:03:30.948+05:30"}');

My data is as below

id	data
25	{"_id": 25, "start": "2023-07-03T22:03:30.948+05:30", "indicator 1": "yes", "indicator 2": "yes", "_validation_status": {"uid": "validation_status_on_hold", "color": "#0000ff", "label": "On Hold", "by_whom": "super_admin", "timestamp": 1688643788}}
26	{"_id": 26, "start": "2023-07-03T22:03:30.948+05:30", "indicator 1": "yes", "indicator 2": "no", "_validation_status": {"uid": "validation_status_on_hold", "color": "#0000ff", "label": "On Hold", "by_whom": "super_admin", "timestamp": 1688643788}}

Data is require in this format

id	attributtename	value
25	_id	25
25	start	2023-07-03T22:03:30.948+05:30
25	indicator 1	yes
25	indicator 2	yes
25	label	On Hold
26	_id	26
26	start	2023-07-03T22:03:30.948+05:30
26	indicator 1	yes
26	indicator 2	no
26	label	On Hold

Marmite Bomber · Accepted Answer · 2023-07-06 14:26:07Z

1

Use jsonb_each_text to extract the key and values from the JSON

select id, key, value
from mytable,
jsonb_each_text(mytable.data)
where key != '_validation_status'

id|key        |value                        |
--+-----------+-----------------------------+
25|_id        |25                           |
25|start      |2023-07-03T22:03:30.948+05:30|
25|indicator 1|yes                          |
25|indicator 2|yes                          |
26|_id        |26                           |
26|start      |2023-07-03T22:03:30.948+05:30|
26|indicator 1|yes                          |
26|indicator 2|no                           |

answered Jul 6, 2023 at 14:26

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

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

Collectives™ on Stack Overflow

Dynamic json data fields extract into columns in postgres

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