Add data type to JSON values query-CodePudding

I am building the following view on SQL Server. The data is extracted from Soap API via Data Factory and stored in SQL table. I union two pieces of the same code because I am getting outputs from API as a Objects or Arrays.

This query works, however when try to order or filter the view got this error:

JSON text is not properly formatted. Unexpected character '.' is found at position 12.

/*Object - invoiceDetails*/
SELECT 
        XML.[CustomerID],
        XML.[SiteId],
        XML.[Date],
        JSON_VALUE(m.[value], '$.accountNbr') AS [AccountNumber],
        JSON_VALUE(m.[value], '$.actualUsage') AS [ActualUsage]

FROM stage.Bill XML
    CROSS APPLY openjson(XML.xmldata) AS n
    CROSS APPLY openjson(n.value, '$.invoiceDetails') AS m
    WHERE XML.XmlData IS NOT NULL
AND ISJSON (XML.xmldata) > 0
AND n.type = 5
AND m.type = 5

UNION ALL

/*Array - invoiceDetails*/
    SELECT 
        XML.[CustomerID],
        XML.[SiteId],
        XML.[Date],
        JSON_VALUE(o.[value], '$.accountNbr') AS [AccountNumber],
        JSON_VALUE(o.[value], '$.actualUsage') AS [ActualUsage]

FROM stage.Bill XML
    CROSS APPLY openjson(XML.xmldata) AS n
    CROSS APPLY openjson(n.value) AS m
    CROSS APPLY openjson(m.value, '$.invoiceDetails') AS o
    WHERE XML.XmlData IS NOT NULL
AND ISJSON (XML.xmldata) > 0
AND n.type = 4

Just did a small exercise using the WITH clause in order to specify data types to the values and noticed I am able to order and filter the view. So I believe the way is to add data type to this query. My problem now is that I don't know how to add WITH clause to my query in order to make it work.

Any advice?

My apologies, you might find the XML naming and prefixes confusing. In my first tests, I supposed to received XML data from Data Factory so my table and columns includes XML prefixes untill I noticed Data Factory delivers JSON data, so started query data as JSON but I have not changed table name and prefixes.

Thank you for your comments to enrich this question. I took this example below from a YouTube channel (Not sure if I am allowed to mention channel's name:https://www.youtube.com/watch?v=yl9jKGgASTY&t=474s). I believe it is a similar approach. Could you please help how could I add the WITH clause in order to specify data types?

DECLARE @json NVARCHAR(MAX)
SET @json =
 N'{
    "OrderHeader": [
        {
            "OrderID": 100,
            "CustomerID": 2000,
            "OrderDetail": [
                {
                    "ProductID": 2000,
                    "UnitPrice": 350
                },
                {
                    "ProductID": 5000,
                    "UnitPrice": 800
                },
                {
                    "ProductID": 9000,
                    "UnitPrice": 200
                }
            ]
        }
    ]
}'

SELECT
    JSON_VALUE(a.value, '$.OrderID') AS OrderID,
    JSON_VALUE(a.value, '$.CustomerID') AS CustomerID,
    JSON_VALUE(a.value, '$.ProductID') AS ProductID,
    JSON_VALUE(a.value, '$.UnitPrice') AS UnitPrice
FROM OPENJSON(@json, '$.OrderHeader') AS a
CROSS APPLY OPENJSON(a.value, 'OrderDetail') AS b

CodePudding user response：

You have a couple of typos.

The second OPENJSON should have the path starting $.
The third and fourth SELECT columns should use b.value not a.value

DECLARE @json NVARCHAR(MAX)
SET @json =
 N'{
    "OrderHeader": [
        {
            "OrderID": 100,
            "CustomerID": 2000,
            "OrderDetail": [
                {
                    "ProductID": 2000,
                    "UnitPrice": 350
                },
                {
                    "ProductID": 5000,
                    "UnitPrice": 800
                },
                {
                    "ProductID": 9000,
                    "UnitPrice": 200
                }
            ]
        }
    ]
}'

SELECT
    JSON_VALUE(a.value, '$.OrderID') AS OrderID,
    JSON_VALUE(a.value, '$.CustomerID') AS CustomerID,
    JSON_VALUE(b.value, '$.ProductID') AS ProductID,
    JSON_VALUE(b.value, '$.UnitPrice') AS UnitPrice
FROM OPENJSON(@json, '$.OrderHeader') AS a
CROSS APPLY OPENJSON(a.value, '$.OrderDetail') AS b;

An alternative syntax is to use OPENJSON on each object with an explicit schema

SELECT
    oh.OrderID,
    oh.CustomerID,
    od.ProductID,
    od.UnitPrice
FROM OPENJSON(@json, '$.OrderHeader')
  WITH (
    OrderID int,
    CustomerID int,
    OrderDetail nvarchar(max) AS JSON
  ) AS oh
CROSS APPLY OPENJSON(oh.OrderDetail)
  WITH (
    ProductID int,
    UnitPrice decimal(18,9)
  ) AS od;

db<>fiddle