How to get the detail row number for rows in JSON string

Question

I have a stored procedure that accepts a NVARCHAR(max) string that is JSON data that I need to validate before loading it into the live tables. If the validation fails I need to return a message with the issue and the row number of the bad data row. The rows do not have a number assigned in the JSON string but it is implied by the order that they are stored in the string. I am trying to assign a incremental number during the OPENJSON function.

When using XML I can do this:

SELECT ROW_NUMBER() OVER (ORDER BY item) AS rowOrder 
     , item.value('(./Id/text())[1]', 'bigInt') AS EId
     , item.value('(./Name/text())[1]', 'nvarchar(255)') AS EName
     , item.value('(./Number/text())[1]', 'nvarchar(30)') AS ENumber
FROM @ERow.nodes('/variable/item') AS main(item);

to derive it but that technique does not work with OPENJSON

I would rather not do it in two passes if possible - i.e. Load the data into a temp table and then update the rows in the temp table with a row number

SELECT ROW_NUMBER() OVER () AS rownum
     , newColumnName
     , decimal_column
     , nvarchar_column 
FROM OPENJSON(@JSON_String)
WITH (
    newColumnName BIGINT '$.id_column',
    decimal_column  DEC(28,8),
    nvarchar_column NVARCHAR(30)
)

Was thinking this would work but no luck.

Would like to end up with a table like

Psalms

What does the string look like? – Aconcagua

Answer 1

OpenJson without WITH clause will return a table containing key, value and type columns, where key is the key of the array (integer) or object (string).

You can process the value any way you like:

SELECT j1.[key]
     , j2.*
     , JSON_VALUE(j1.[value], '$.nvarchar_column') AS [another example]
FROM OPENJSON('[
    {"id_column": 1234, "decimal_column": 12.34, "nvarchar_column": "asdf"},
    {"id_column": 5678, "decimal_column": 56.78, "nvarchar_column": "fdsa"}
]') AS j1
CROSS APPLY OPENJSON (j1.[value]) WITH (
    id_column BIGINT,
    decimal_column DEC(28, 8),
    nvarchar_column NVARCHAR(30)
) AS j2

Aconcagua

@SalmonA . . . So the "key name" for an array is the index into the array. That makes sense. – Alveolate

@gordon yes, it is 0 based just like JavaScript arrays. – Aconcagua

@KeithMiller . . . This is the best answer, but you might want to add 1 to the key. – Alveolate

Answer 2

I don't think SQL Server has any sort of WITH ORDINAL option on OPENJSON().

You can try this:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum, 

The ORDER BY is required and the (SELECT NULL) is recognized as a constant so nothing is actually sorted. In practice, this should return the results in the order returned in the JSON, but I don't think this is guaranteed.

Alveolate

I was so close. Seems to work consistently in my test. – Psalms

@KeithMiller . . . The situations where it might not work are (1) when you add an order by to the outer query but sill want the row_number() in the original order or (2) when the from clause is being processed in parallel. I don't think OPENJSON() runs in parallel, so you might be relatively safe. – Alveolate