Add data type to JSON values query

Question

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

I must admit, `ISJSON (XML.xmldata)` is very confusing; why do you have a table aliased `XML` with a column called `xmldata` that has JSON data in it, rather than `xml` data? — Thom A, Jun 10 '22 at 16:19
As for the error, just because you have `ISJSON (XML.xmldata) > 0` in the `WHERE` doesn't mean that SQL Server *won't* attempt to parse those values as valid JSON values first. It could easily attempt to perform the `OPENJSON` calls against the data before filtering, for example. — Thom A, Jun 10 '22 at 16:21
You right. My apologies, it is 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. — David, Jun 10 '22 at 16:41
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Jun 10 '22 at 17:29

score 0 · Accepted Answer · answered Jun 12 '22 at 16:08

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

Thank you so much for your answer, really helped to solve my issue. — David, Jun 24 '22 at 16:15
I am glad to vote as an answer but not able to do it because my reputation points are low by now. I am new here :( — David, Jun 24 '22 at 17:56

Add data type to JSON values query

1 Answers1