Enumerating JSON arrays with OPENJSON

Question

I've been working on converting some of my XML based stored procedures to JSON, but I'm running into an issue.

With OPENXML, I had access to mp:id, so when I was parsing an XML, I could access an element and its position in the XML in my query.

I'd like to do something similar with OPENJSON. Here's an example

DECLARE @JSON VARCHAR(MAX) =
'[
  {"Fruit":"Apple"},
  {"Fruit":"Banana"},
  {"Fruit":"Pineapple"}
]'

SELECT Fruit
FROM OPENJSON(@JSON,'$')
    WITH (Fruit VARCHAR(24) '$.Fruit')

For each fruit, I'd like to have its position as well (i.e., 1 for apple, 2 for banana, etc).

Does anyone know how to do this with OPENJSON?

Answer 1

Use OPENJSON without the WITH to project the position of each element. eg

DECLARE @JSON VARCHAR(MAX) =
'[
  {"Fruit":"Apple"},
  {"Fruit":"Banana"},
  {"Fruit":"Pineapple"}
]'

SELECT cast([key] as int) ix, json_value(value,'$.Fruit') Fruit
FROM OPENJSON(@JSON,'$')
    

outputs

ix          Fruit
----------- -------------
0           Apple
1           Banana
2           Pineapple