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

Oto Shavadze · Accepted Answer · 2017-07-11 07:23:32Z

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

answered Jul 11, 2017 at 7:23

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thirupathi Thangavel Over a year ago

Wow! You're awesome! Thanks for the info :)

Collectives™ on Stack Overflow

json extract multiple level value in sql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related