How can I map the key in an openjson query with an explicit schema?

Question

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]')

Answer 1

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