json extract multiple level value in sql

Question

This is a follow-up question of Extract all values from json in sql table

What if the json value has multiple levels?

For example,

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

In this case, I want to extract the price for all the items. But I get error when I run the below query.

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))

I get the below error.

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

If I remove one json_each(), it throws

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

Answer 1

You can use lateral join, something like:

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