Now that SQL Server is working better with JSON it is becoming easier to just
put plain JSON into columns. It’s nice to be able to query the JSON directly
in the database. The other day I was wanted to get a list of values in arrays.
Here’s how I did it.
First let’s set up the table:
DECLARE @JSON TABLE
( JsonID INT PRIMARY KEY IDENTITY(1, 1)
, JsonData nvarchar(max) NOT NULL
);
Some test data:
INSERT INTO @JSON (JsonData)
VALUES
(N'{"json":[{"name": 1},{"name": 2}]}'),
(N'{"json":[{"name": 3},{"name": 4}]}'),
(N'{"json":[{"name": 4},{"name": 5}]}');
To get the arrays we need to use a JSON_QUERY function which let’s us get at
inner JSON.
SELECT JSON_QUERY(t.JsonData, '$.json') arrays
FROM @JSON t
WHERE t.JsonData IS NOT NULL
Now to get the values in the arrays we need to use the JSON_VALUE function.
This is where it gets tricky. You can’t just simply use the JSON_VALUE
function. You can’t make the query above the FROM clause. You must use CROSS
APPLY
SELECT n.[Name]
FROM @JSON t
CROSS APPLY (
SELECT JSON_VALUE(a.value, '$.name') [Name]
FROM OPENJSON((
SELECT JSON_QUERY(t.JsonData, '$.json') JsonArray ), '$') a
) n
Now, let’s make sure that there are no null values and that we don’t repeat
any of the values.
SELECT n.[Name]
FROM @JSON t
CROSS APPLY (
SELECT JSON_VALUE(a.value, '$.name') [Name]
FROM OPENJSON((
SELECT JSON_QUERY(t.JsonData, '$.json') JsonArray ), '$') a
) n
WHERE t.JsonData IS NOT NULL
GROUP BY n.[Name]