Retrieve specific JSON properties from JSON collection in SQL Server column using a SQL query

I am struggling to find a solution to retrieve specific properties from a JSON collection which is stored in a SQL Server table's column.

Please see my table structure here:

Id: int
ItemDetails: NVARCHAR(MAX)

Below is how the table would be populated. Apologies I couldn't make it show in the HTML Table format as there is no way to Insert JSON in the tabular format in stackoverflow.

Id	Name
1	[ { "name": "Sam", "age": 14, "gender": "Male" }, { "name": "Sandra", "age": 15, "gender": "Female" } ]
2	[ { "name": "Tom", "age": 22, "gender": "Male" }, { "name": "Caroline", "age": 25, "gender": "Female" } ]

Now I want to write a SQL query to get output like this:

Id	Name
1	Sam
1	Sandra
2	Tom
2	Caroline

If the ItemDetails column didn't store a JSON collection, but instead a single JSON object, then this can be achievable using JSON_VALUE. However because it contains a collection, I haven't been able to crack it.

I have tried using JSON_EXTRACT or JSON_QUERY as well but all in to no vain. Please could someone assist here?

Solution

You may try to parse this JSON content with OPENJSON() and explicit schema:

SELECT v.Id, j.Name, j.Age
FROM (VALUES
   (1, N'[ { "name":"Sam", "age":14, "gender":"Male" }, { "name":"Sandra", "age":15, "gender":"Female" } ]'),
   (2, N'[ { "name":"Tom", "age":22, "gender":"Male" }, { "name":"Caroline", "age":25, "gender":"Female" } ]')
) v (Id, ItemDetails)
CROSS APPLY OPENJSON(v.ItemDetails) WITH (
   Name nvarchar(100) '$.name',
   Age int '$.age'
   -- add additional columns definitions
) j