The inventory system I am using, has changed the way of storing the invoice lines and as you can see below the JSON array (Lines) has been converted into a string.
[
{
"TaskID": "06896b8d-770e-426f-ac82-000183252c47",
"InvoiceNumber": "INV-9471D",
"Lines": "[{"ProductID":"a2540764-3e28-41f0-9ae8-c348a49ee941","SKU":"BP13V1","Name":"Biodynamic Whole Red Lentils 1kg","Quantity":1.0,"Price":11.95,"Discount":0.0,"Tax":0.0,"Total":11.95,"AverageCost":8.9882,"TaxRule":"GST Free Income","Account":"202","ProductCustomField10":""},{"ProductID":"aae9e53e-ad96-493c-99e3-1de193961fa8","SKU":"BP6V1","Name":"Organic Borlotti Beans 1kg","Quantity":1.0,"Price":9.25,"Discount":0.0,"Tax":0.0,"Total":9.25,"AverageCost":5.8832,"TaxRule":"GST Free Income","Account":"202","ProductCustomField10":""}]",
"TotalBeforeTax": 274.29,
"Tax": 2.04,
"Total": 276.33,
"Paid": 276.33
}
]
To parse the JSON lines, I created the following script that now returns a NULL instead of a specific value due too the change explained above:
SELECT
dbo.Sale.[ID],
[Invoices_Header].Inv_Number AS Doc_Num,
[Invoices_Lines].[ProductID],
[Invoices_Lines].[QTY],
[Invoices_Lines].[Price],
[Invoices_Lines].[Discount],
[Invoices_Lines].[Tax],
[Invoices_Lines].[TaxRule],
[Invoices_Lines].[Account]
FROM dbo.Sale
OUTER APPLY OPENJSON([Invoices], '$')
WITH (
Inv_Number VARCHAR(100) '$.InvoiceNumber'
) AS [Invoices_Header]
OUTER APPLY OPENJSON([Invoices], '$.Lines')
WITH (
ProductID VARCHAR(100) '$.ProductID',
SKU VARCHAR(100) '$.SKU',
ProductName VARCHAR(100) '$.Name',
QTY DECIMAL (8,4) '$.Quantity',
Price NUMERIC(19,4) '$.Price',
Discount NUMERIC(5,2) '$.Discount',
Tax NUMERIC(19,4) '$.Tax',
Total NUMERIC(19,4) '$.Total',
TaxRule VARCHAR(100) '$.TaxRule',
Account VARCHAR(100) '$.Account',
AverageCost NUMERIC(19,4) '$.AverageCost'
) AS [Invoices_Lines]
The final result should look like this:
| ID | DOC_NUM | Product_ID | Qty | Price | Discount | Tax | Tax Rule | Account | Average Cost |
|---|---|---|---|---|---|---|---|---|---|
| 06896B8D-770E-426F-AC82-000183252C47 | INV-9471D | a2540764-3e28-41f0-9ae8-c348a49ee941 | 1 | 11.95 | 0 | 0 | GST Free | 202 | 8.97 |
| 06896B8D-770E-426F-AC82-000183252C47 | INV-9471D | aae9e53e-ad96-493c-99e3-1de193961fa8 | 1 | 9.25 | 0 | 0 | GST Free | 202 | 7.5 |
Instead of this:
| ID | DOC_NUM | Qty | Price | Discount | Tax | Tax Rule | Account | Average Cost |
|---|---|---|---|---|---|---|---|---|
| 06896B8D-770E-426F-AC82-000183252C47 | INV-9471D | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 06896B8D-770E-426F-AC82-000183252C47 | INV-9471D | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
Is there a way of modifying the script above to properly parse the values from the JSON array that has been converted into a string?
Regards
David
Since the nested JSON is stored as a string in the outer JSON, the contained values cannot be accessed directly by parsing the Invoices column. Instead, you should add a '$.Lines' reference to the outer JSON column list, and then reference that column in the inner JSON using OPENJSON(Invoices_Header.Lines, '$')
This also properly handles any cases where the outer JSON contains more than one array element. The inner JSON reflects the Lines value for the current outer JSON element (matching the selected InvoiceNumber.
SELECT
dbo.Sale.ID,
Invoices_Header.Inv_Number AS Doc_Num,
Invoices_Lines.ProductID,
Invoices_Lines.QTY,
Invoices_Lines.Price,
Invoices_Lines.Discount,
Invoices_Lines.Tax,
Invoices_Lines.TaxRule,
Invoices_Lines.Account,
Invoices_Lines.AverageCost -- *** Added ***
FROM dbo.Sale
OUTER APPLY OPENJSON(Invoices, '$')
WITH (
Inv_Number VARCHAR(100) '$.InvoiceNumber',
Lines NVARCHAR(MAX) '$.Lines' -- *** Added ***
) AS Invoices_Header
OUTER APPLY OPENJSON(Invoices_Header.Lines, '$') -- *** Changed ***
WITH (
ProductID VARCHAR(100) '$.ProductID',
SKU VARCHAR(100) '$.SKU',
ProductName VARCHAR(100) '$.Name',
QTY DECIMAL (8,4) '$.Quantity',
Price NUMERIC(19,4) '$.Price',
Discount NUMERIC(5,2) '$.Discount',
Tax NUMERIC(19,4) '$.Tax',
Total NUMERIC(19,4) '$.Total',
TaxRule VARCHAR(100) '$.TaxRule',
Account VARCHAR(100) '$.Account',
AverageCost NUMERIC(19,4) '$.AverageCost'
) AS Invoices_Lines
Note: If the nested JSON was exposed as just regular nested elements instead of as a quoted string value, we would use the AS JSON option when extracting the Lines content. Since the nested value is a string for this case, we do not use that option.
The missing AverageCost column reference has also been added to the final select list, and unnecessary name quoting has been removed to improve readability.
See this db<>fiddle for a demo.
2