Xero Invoice Line Items via Stitch Data and BigQuery.sql

-- Having extracted Xero data into BigQuery (via Stitch Data), extracting the Invoice Line details is still quite tricky.
-- On the invoices table, the invoice lines are stored as a nested JSON object: lineitems
-- The following query produces a row for each Invoice Line, and also unpacks Tracking fields

-- this line handles the easy single-field selections
SELECT invoiceid, invoicenumber, lineitems.lineamount, lineitems.description as linedescription
-- unpack the sub-nested lineitems.tracking nodes for each invoice, and return the option field for a specific name. 
-- LIMIT 1 ensures we only return the first match. SQL will break if more than one result is returned.
, ( SELECT option from UNNEST ( lineitems.tracking ) WHERE name = 'Studio' LIMIT 1) as Studio
-- repeat the unpack for another tracking name
, ( SELECT option from UNNEST ( lineitems.tracking ) WHERE name = 'Type' LIMIT 1) as Type
-- FROM specifies the core invoices table
FROM `xerobigquery-222300.xeroint.invoices` r
-- connect invoices (from above) to an unpacked list of lineitems
LEFT JOIN UNNEST ( r.lineitems ) as lineitems
-- debug code to isolate particular invoices
--where invoicenumber IN (  'INV-21068' , 'STIN18173' )
-- debug code to limit the number of rows
--LIMIT 1000

-- Compact version:
SELECT invoiceid, invoicenumber, lineitems.lineamount, lineitems.description as linedescription
  , ( SELECT option from UNNEST ( lineitems.tracking ) WHERE name = 'Studio' LIMIT 1) as Studio
  , ( SELECT option from UNNEST ( lineitems.tracking ) WHERE name = 'Type' LIMIT 1) as Type
FROM `xerobigquery-222300.xeroint.invoices` r
LEFT JOIN UNNEST ( r.lineitems ) as lineitems

You can also use the Skyvia - powerfull cloud service to work with Bigquery and Xero, it is free and supports sql query both in the form of code and in a browser-based simple interface without coding

Skyvia