我想在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格式的。

最佳答案

使用json_array_elements()

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/

10-14 15:16