Ad

BigQuery / Shopify - Get Tracking Numbers With Order #

I have a BigQuery dataset exported from Shopify I've already narrowed down my dataset to orders between a certain date span with order number & an array of fulfillment objects. I'm now needing to get back a list of tracking numbers and order numbers.

Sample Data

[
  {
    "order_number": "5545",
    "fulfillments": [
      {
        "tracking_url": null,
        "id": "617029074993",
        "tracking_company": "ups",
        "tracking_number": "Z1234567890"
      },
      {
        "tracking_url": null,
        "id": "617029075334",
        "tracking_company": "ups",
        "tracking_number": "Z1234988901"
      },
      {
        "tracking_url": null,
        "id": "617029036534",
        "tracking_company": "ups",
        "tracking_number": "Z1234987801"
      }
    ],
    "_sdc_batched_at": "2018-11-10 02:46:24.270 UTC",
    "_sdc_sequence": "1541817507934"
  },
  {
    "order_number": "2214",
    "fulfillments": [
      {
        "tracking_url": null,
        "id": "617029223993",
        "tracking_company": "ups",
        "tracking_number": "Z1234456890"
      },
      {
        "tracking_url": null,
        "id": "617024455334",
        "tracking_company": "ups",
        "tracking_number": "Z1234780901"
      }
    ],
    "_sdc_batched_at": "2018-11-10 02:46:21.270 UTC",
    "_sdc_sequence": "1541817545934"
  }
]

Expected Result

Each row would be an order number & tracking number combination

[
  {
    "order_number": "5545",
    "tracking_number": "Z1234567890"
  },
  {
    "order_number": "5545",
    "tracking_number": "Z1234988901"
  },
  {
    "order_number": "5545",
    "tracking_number": "Z1234987801"
  },
  {
    "order_number": "2214",
    "tracking_number": "Z1234456890"
  },
  {
    "order_number": "2214",
    "tracking_number": "Z1234780901"
  }
]
Ad

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT order_number, fulfillment.tracking_number
FROM `project.dataset.table`,
  UNNEST(fulfillments) fulfillment
Ad
source: stackoverflow.com
Ad