从Postgres中的JSON内部阵列中选择键

Question

我有一个称为 props的JSONB列，看起来像这样：

{"house_color": "white", "dynamic_properties": [{ "visibility" : "blue" }, { "autonomy" : "self"}]}

现在，我知道如何通过这样做来获取所有房屋的所有颜色：

select "props"->"house_color" as color FROM houses

现在，我正在尝试通过在选择中执行某种类型的条件来获得所有可见性动态属性，而我被卡住了。因此，在示例中，我会得到"蓝色"。我正在寻找

之类的东西

select "props"->"dynamic_properties"->"visibility" as color FROM houses

，但我不知道该如何在该 dynamic_properties数组中的对象中看。

Answer 1

demo：db＆lt;> fiddle

WITH jsondata AS (
    SELECT '{"house_color": "white", "dynamic_properties": [{ "visibility" : "blue" }, { "autonomy" : "self"}]}'::json as data
)
SELECT
    data ->> 'house_color'
FROM jsondata, json_array_elements(data -> 'dynamic_properties') as elem
WHERE elem ->> 'visibility' = 'blue'

json_array_elements每个元素将数组扩展为一行。因此，在您的示例中，结果是两个行。这些行可以通过其键/值对过滤。