T-SQL: How Can I Parse a JSON Array and Extract a Single Property Value?

Question

Given that:

SELECT * FROM OPENJSON(JSON_QUERY('[{"JobId":"2838","Options":1}, {"JobId":"2839","Options":1}]'))

Gives us:

key value                           type
0   {"JobId":"2838","Options":1}    5
1   {"JobId":"2839","Options":1}    5

---

How can I change my query to return the job ids?

value
2838
2839

Answer 1

This should do it

SELECT JobId
FROM OPENJSON('[{"JobId":"2838","Options":1}, {"JobId":"2839","Options":1}]')
WITH (JobId INT N'$.JobId');