Shopify Question posted in
The Shopify help center can be found here.

How to extract data from Nested Arrays 3-4 deep – Shopify

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
  }
Tags:

2

Answers


  1. 0

    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.

    create temp table foo(v variant);

insert into foo select parse_json('<your json>');

select   VALUE:line_item.price::double          as PRICE
        ,VALUE:line_item.sku::string            as SKU
        ,VALUE:line_item.product_id::string     as PRODUCT_ID
from foo, lateral flatten (v:refund_line_items);
    Login or Signup to reply.
  2. 0

    You can use RECURSIVE => TRUE flag for FLATTEN() function to expand all the nested levels.

    select *  from ORDERS
, lateral flatten(input => v, RECURSIVE => TRUE);

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search