在JSON中选择数据
问题描述:
我想在Postgres中使用SQL来提取数据,但希望进入JSON结果并只提取我需要的数据。在JSON中选择数据
如果我写(在瓦伦蒂娜工作室):
Select "data" from "cars"
第一行是这样的:
[{"Model": "Golf", "Make": "VW", "Engine": "2.9"},
{"Model": "M3", "Make": "BMW", "Engine": "3.0"}]
我想什么是简单的:
Golf, M3
或"Golf", "M3"
然后我也可以对012使用相同的方法或"Engine"
本质上我不希望我的结果在JSON中。
答
with cars(data) as (
values
('[
{"Model": "Golf", "Make": "VW", "Engine": "2.9"},
{"Model": "M3", "Make": "BMW", "Engine": "3.0"}
]'::json)
)
select
elem->>'Model' as model,
elem->>'Make' as make,
elem->>'Engine' as engine
from cars,
lateral json_array_elements(data) elem
model | make | engine
-------+------+--------
Golf | VW | 2.9
M3 | BMW | 3.0
(2 rows)
答
SELECT string_agg(x->>'Model', ',')
FROM cars
CROSS JOIN LATERAL
jsonb_array_elements(data) x
GROUP BY cars;
┌────────────┐
│ string_agg │
├────────────┤
│ Golf,M3 │
└────────────┘
(1 row)
答
我想我也得到了我想要的东西(和融化它,我正打算做的方式)与:从上面我
select obj->'Model' as model, obj->'Make' as make,
obj->'Engine' as engine from data n, jsonb_array_elements(n.DATA) as obj
还可以使用:
select
elem->>'Model' as model,
elem->>'Make' as make ,
elem->>'Engine' as engine
from cars,
lateral jsonb_array_elements(data) elem;
感谢您的帮助!