This is my first time posting here, so please let me know if you need more info. I am using stitch and snowflake to query my Shopify data.
I am trying to extract the price, sku, and product_id in LINE_ITEM from a nested array 3 levels down. ORDERS table -> REFUNDS -> REFUND_LINE_ITEMS -> LINE_ITEM. I am successful in extracting data from the first level of the nested array REFUNDS but not REFUND_LINE_ITEM, let alone LINE_ITEM. I would appreciate any help!
select *,
l.value::variant as "line_item"
from (
select o.id as order_id,
r.value:id::string as id,
e.value::variant as "refund_line_items"
from ORDERS o
, lateral flatten(input => refunds) r
, lateral flatten(input => r.value:refund_line_items) e
where financial_status = 'refunded') o
, lateral flatten(input => refund_line_items) r
, lateral flatten(input => r.value:line_item) l;
Here is an example of ORDERS table data.
{
"created_at": "2022-10-20T16:20:15Z",
"id": 69130400,
"order_adjustments": [],
"refund_line_items": [{
"id": 223792431,
"line_item": {
"discount_allocations": [{
"amount": 1.5,
"amount_set": {
"presentment_money": {
"amount": "1.50",
"currency_code": "USD"
},
"shop_money": {
"amount": "1.50",
"currency_code": "USD"
}
},
"discount_application_index": 0
}],
"fulfillable_quantity": 0,
"fulfillment_service": "manual",
"fulfillment_status": "fulfilled",
"id": 60942933,
"name": "widget a",
"pre_tax_price": 13.5,
"pre_tax_price_set": {
"presentment_money": {
"amount": "13.50",
"currency_code": "USD"
},
"shop_money": {
"amount": "13.50",
"currency_code": "USD"
}
},
"price": 15,
"price_set": {
"presentment_money": {
"amount": "15.00",
"currency_code": "USD"
},
"shop_money": {
"amount": "15.00",
"currency_code": "USD"
}
},
"product_exists": false,
"product_id": "122345",
"quantity": 1,
"sku": "118882",
"tax_lines": [{
"price": 0.81,
"price_set": {
"presentment_money": {
"amount": "0.81",
"currency_code": "USD"
},
"shop_money": {
"amount": "0.81",
"currency_code": "USD"
}
},
"rate": 0.06,
"title": "CA STATE TAX"
},
{
"price": 0.07,
"price_set": {
"presentment_money": {
"amount": "0.07",
"currency_code": "USD"
},
"shop_money": {
"amount": "0.07",
"currency_code": "USD"
}
},
"rate": 0.005,
"title": "CA SPECIAL TAX"
},
{
"price": 0.03,
"price_set": {
"presentment_money": {
"amount": "0.03",
"currency_code": "USD"
},
"shop_money": {
"amount": "0.03",
"currency_code": "USD"
}
},
"rate": 0.0025,
"title": "CA COUNTY TAX"
},
{
"price": 0.14,
"price_set": {
"presentment_money": {
"amount": "0.14",
"currency_code": "USD"
},
"shop_money": {
"amount": "0.14",
"currency_code": "USD"
}
},
"rate": 0.01,
"title": "CA SPECIAL TAX"
}
],
"taxable": true,
"title": "widget A",
"total_discount": 0,
"total_discount_set": {
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
},
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"variant_id": null,
"variant_inventory_management": null,
"variant_title": "",
"vendor": "Company XXX"
},
"line_item_id": 60942933,
"location_id": null,
"quantity": 1,
"restock_type": "no_restock",
"subtotal": 13.5,
"total_tax": 1.05
}],
"restock": false,
"user_id": 3947371
}
2
Answers
One thing that helps parse multi-level JSON… Start with a
select *
on the flattened array. That exposes a number of columns that Snowflake uses internally when parsing the JSON that you can read and break down.You can use
RECURSIVE => TRUE
flag forFLATTEN()
function to expand all the nested levels.The resulted table would flatten all the levels and contain additional fields KEY, PATH, VALUE and few others that you can filter on and get access to the data on the required level.