I have a query that takes an array of a explicit number of (let's say 100) arrays of a sequence of values. the json looks something like this
[["horse",6],
...,
["dog",100]]
Each of these array elements maps directly to some other table. While I can do the following to do the update, I'd prefer to be able to use an explicit schema.
update some_table
set favorite_animal=convert(varchar(50),json_value(value,'strict $[0]'))
,favorite_number=convert(int,json_value(value,'strict $[1]'))
from openjson(@json)
where id = convert(int,[key])
Unfortunately, when I use an explicit schema, I'm not sure how to specify the current path (the value in [key]
).
I'd really like if I could do something like this but I can't find the syntax:
from openjson(@json)
with (favAnimal varchar(50) '$[0]',favNumber int '$[1]', row int '[key]')
Original answer:
Next approach, which is one possible solution, is to use OPENJSON()
and two additional CROSS APPLY
operators for elements with index 0
and 1
from your nested JSON arrays.
T-SQL:
DECLARE @json nvarchar(max)
SET @json = N'[["horse",6],["cat",10],["dog",100]]'
SELECT
(j.[key] + 1) AS row,
j1.[value] AS favAnimal,
j2.[value] AS favNumber
FROM OPENJSON(@json) j
CROSS APPLY OPENJSON(j.[value]) j1
CROSS APPLY OPENJSON(j.[value]) j2
WHERE (j1.[key] = 0) AND (j2.[key] = 1)
Result:
-------------------------
row favAnimal favNumber
-------------------------
1 horse 6
2 cat 10
3 dog 100
Update:
I think that even this should work:
DECLARE @json nvarchar(max)
SET @json = N'[["horse",6],["cat",10],["dog",100]]'
SELECT
(j1.[key] + 1) AS [row],
j2.*
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(CONCAT(N'[', j1.[value], N']')) WITH (
favAnimal nvarchar(50) '$[0]',
favNumber int '$[1]'
) j2
Result:
-------------------------
row favAnimal favNumber
-------------------------
1 horse 6
2 cat 10
3 dog 100
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With