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

squillman · Accepted Answer · 2019-05-31 16:49:29Z

3

This should do it

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

answered May 31, 2019 at 16:49

squillman

13.7k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1