英文:
Dynamic json data fields extract into columns in postgres
问题
我想从JSON中提取数据并放入列中。
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"}');
我的数据如下
| 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}} |
数据需要以此格式
| id | attributename | 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 |
英文:
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 |
答案1
得分: 1
使用jsonb_each_text来从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 |
英文:
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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论