SQL: JSON Extract from nested object

Question

I have a table like this

id	obj
1	{"is_from_shopping_bag":true,"products":[{"price":{"amount":"18.00","currency":"USD","offset":100,"amount_with_offset":"1800"},"product_id":"1234","quantity":1}],"source":"cart"}
2	{"is_from_shopping_bag":false,"products":[{"price":{"amount":"80.00","currency":"USD","offset":100,"amount_with_offset":"8000"},"product_id":"2345","quantity":1}],"source":"pdp"}

I am doing a sql query in Hive to get the 'currency' field.

Currently I can run

SELECT
    JSON_EXTRACT( obj, '$.products')
FROM my_table

Which returns

obj
[{"price":{"amount":"18.00","currency":"USD","offset":100,"amount_with_offset":"1800"},"product_id":"1234","quantity":1}]
[{"price":{"amount":"80.00","currency":"USD","offset":100,"amount_with_offset":"8000"},"product_id":"2345","quantity":1}]

How do I go a layer deeper to get the currency?

ggordon · Accepted Answer · 2021-06-11 00:12:24Z

1

To get the currency of the first product use:

SELECT
    id,JSON_EXTRACT( obj, '$.products[0].price.currency') first_product_currency
FROM my_table;

id	first_product_currency
1	"USD"
2	"USD"

To get the currency of all the products use:

SELECT
    id,JSON_EXTRACT( obj, '$.products[*].price.currency') multiple_currencies
FROM my_table;

id	multiple_currencies
1	["USD"]
2	["USD"]

answered Jun 11, 2021 at 0:12

ggordon

10.1k2 gold badges19 silver badges29 bronze badges

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

1 Answer 1