Sample Header Ad - 728x90

Enumerating JSON arrays with OPENJSON

1 vote
1 answer
1414 views
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?
Asked by Rushabh Mehta (251 rep)
Dec 15, 2021, 08:55 PM
Last activity: Dec 16, 2021, 08:07 AM