I am needing to unnest a multidimensional array with multiple items.

My data looks like this (two rows):

Each order in this table has a fulfillments column which is a multidimensional array.

fulfilments has multiple line_items. Some orders only have 1 line_items, some orders have multiple line_items.

My goal is to get each product name and the quantity from each line_items and do a running sum/tally.

Here's a JSON export of the fulfillments column data:

"{
  "fulfillments": [{
    "value": {
      "id": "6575674745",
      "tracking_number": null,
      "line_items": [{
        "value": {
          "properties": [{
            "value": {
              "name": "shipping_interval_unit_type",
              "value": "Months"
            }
          }, {
            "value": {
              "name": "shipping_interval_frequency",
              "value": "1"
            }
          }, {
            "value": {
              "name": "ch_item_id",
              "value": "77321530"
            }
          }],
          "product_id": "4790985097351",
          "total_discount_set": {
            "shop_money": {
              "amount": "0.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "0.00",
              "currency_code": "USD"
            }
          },
          "sku": “10101”,
          "name": “Product1”,
          "variant_id": "33433213108359",
          "quantity": "1",
          "taxable": "true",
          "total_discount": "0",
          "title": "Product1 Title",
          "price_set": {
            "shop_money": {
              "amount": "12.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "12.00",
              "currency_code": "USD"
            }
          },
          "product_exists": "true"
        }
      }],
      "service": "manual",
      "tracking_urls": []
    }
  }, {
    "value": {
      "tracking_url": null,
      "id": "3555531128967",
      "tracking_company": null,
      "tracking_number": null,
      "name": "#3129472402.2",
      "tracking_numbers": [],
      "line_items": [{
        "value": {
          "properties": [{
            "value": {
              "name": "shipping_interval_frequency",
              "value": "1"
            }
          }, {
            "value": {
              "name": "shipping_interval_unit_type",
              "value": "Months"
            }
          }, {
            "value": {
              "name": "ch_item_id",
              "value": "77321529"
            }
          }],
          "product_id": "5216409780359",
          "total_discount_set": {
            "shop_money": {
              "amount": "0.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "0.00",
              "currency_code": "USD"
            }
          },
          "sku": "9005",
          "name": “Product2”,
          "quantity": "1",
          "title": “Product2 Title”,
          "price_set": {
            "shop_money": {
              "amount": "0.00",
              "currency_code": "USD"
            },
            "presentment_money": {
              "amount": "0.00",
              "currency_code": "USD"
            }
          },
          "product_exists": "true"
        }
      }],
      "service": "manual",
      "tracking_urls": []
    }
  }]
}"

SELECT
  fulfillments[ safe_OFFSET (0)].value.line_items[ safe_OFFSET (0)].value.name AS name,
  fulfillments[ safe_OFFSET (0)].value.line_items[ safe_OFFSET (0)].value.quantity AS quantity
FROM
  `mydatabase`

isn't ideal in the event the order has more than one array in line_items

How can I unnest a multidimensional array when I don't know how large each array is?

SELECT
*
FROM
  `mydatabase`,
  UNNEST(fulfillments) as a

Only gives me another array to flatten

CodePudding user response：

Given your scenario, you may try to use this query to unnest line_items with product name and the quantity:

SELECT 
    line_items.value.name,
    line_items.value.quantity,
    line_items.value.product_id

from `mydatabase`, 
    unnest(fulfillments) fulfillments,
    unnest(fulfillments.value.line_items) line_items

Output: