json在sql中提取多级值。

Question

这是Extract all values from json in sql table的后续问题。

如果json值有多个级别怎么办？

例如,

{
    "store-1": {
        "Apple": {
            "category": "fruit",
            "price": 100
        },
        "Orange": {
            "category": "fruit",
            "price": 80
        }
    },
    "store-2": {
        "Orange": {
            "category": "fruit",
            "price": 90
        },
        "Potato": {
            "category": "vegetable",
            "price": 40
        }
    }
}

在这种情况下，我想提取所有项目的价格。但是，当我运行下面的查询时会出现错误。

with my_table(items) as (
    values (
    '{"store-1":{"Apple":{"category":"fruit","price":100},"Orange":{"category":"fruit","price":80}},
    "store-2":{"Orange":{"category":"fruit","price":90},"Potato":{"category":"vegetable","price":40}}}'::json
    )
)

select key, (value->value->>'price')::numeric as price
from my_table,
json_each(json_each(items))

我得到了以下错误。

ERROR:  function json_each(record) does not exist
LINE 10: json_each(json_each(items))

如果我删除一个json_each()，它就会抛出

ERROR:  operator does not exist: json -> json
LINE 8: select key, (value->value->>'price')::numeric as price

Answer 1

您可以使用横向连接，类似于：

with my_table(items) as (
    values (
    '{"store-1":{"Apple":{"category":"fruit","price":100},"Orange":{"category":"fruit","price":80}},
    "store-2":{"Orange":{"category":"fruit","price":90},"Potato":{"category":"vegetable","price":40}}}'::json
    )
)

select outer_key, key, value->>'price' from (
    select key as outer_key, value as val from my_table 
    join lateral json_each(items) 
    on true
)t
join lateral json_each(val) 
on true