我想在Postgres中使用SQL提取数据,但是我想进入JSON结果,只提取我需要的数据。
如果我(在瓦伦蒂娜工作室)写作:
Select "data" from "cars"
第一排看起来像:
[{"Model": "Golf", "Make": "VW", "Engine": "2.9"},
{"Model": "M3", "Make": "BMW", "Engine": "3.0"}]
我想要的只是:
Golf, M3
或"Golf", "M3"
然后我也可以用同样的方法来处理
"Make"
或"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)
关于sql - 在JSON中选择数据,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43612794/