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

What does the string look like? – Salman A Apr 16 '19 at 14:29 — Salman A, Apr 16 '19 at 14:29

score 7 · Accepted Answer · edited Mar 05 '20 at 05:57

7

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

edited Mar 05 '20 at 05:57

N8allan

2,138
19
32

answered Apr 16 '19 at 14:42

Salman A

262,204
82
430
521

@SalmonA . . . So the "key name" for an array is the index into the array. That makes sense. – Gordon Linoff Apr 16 '19 at 15:01
@gordon yes, it is 0 based just like JavaScript arrays. – Salman A Apr 16 '19 at 15:02
@KeithMiller . . . This is the best answer, but you might want to add 1 to the key. – Gordon Linoff Apr 16 '19 at 15:06

score 4 · Answer 2 · answered Apr 16 '19 at 14:28

4

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.

answered Apr 16 '19 at 14:28

Gordon Linoff

1,242,037
58
646
786

I was so close. Seems to work consistently in my test. – Keith Miller Apr 16 '19 at 14:33
@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. – Gordon Linoff Apr 16 '19 at 14:57

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

2 Answers2