In this article I will run through a problem I overcame where I needed to query a JSON array of attributes for each a record.

I was working on a Stored Procedure that was querying JSON using REPLACE, SUBSTRING and a number of CHARINDEX‘s for every attribute. This may have been fine when it was written, but now, with the JSON functions in SQL Server, it can be considered slow, plus to fix the bug I was working on meant slowing it down further.

Solution – Querying JSON

I’ll put the solution first because sometimes you don’t want the waffle.

For this example I will be using this JSON, simple as it is.

{
    "recordAttributes": [{
            "fieldName": "Delivery Method",
            "value": "In Person",
            "sortOrder": 1
        }, {
            "fieldName": "Delivery Person",
            "value": "Jason",
            "sortOrder": 2
        }
    ]
}

So the problem is that the JSON represents an array of array’s, that is, recordAttributes is an array and each item is an array of key-value pairs.

So first of all I use OPENJSON to separate out the first array into it’s own rows, like so…

DECLARE @json NVARCHAR(MAX) = N'{"recordAttributes": [{"fieldName": "Delivery Method","value": "In Person","sortOrder": 1}, {"fieldName": "Delivery Person","value": "Jason","sortOrder": 2}]}'


SELECT * FROM OPENJSON(@json, '$.recordAttributes') 

Which gives the result…

Now I use JSON_VALUE in the WHERE clause to select the correct row, and in the SELECT to get my desired value…

DECLARE @json NVARCHAR(MAX) = N'{"recordAttributes": [{"fieldName": "Delivery Method","value": "In Person","sortOrder": 1}, {"fieldName": "Delivery Person","value": "Jason","sortOrder": 2}]}'

SELECT JSON_VALUE(Value, '$.value') AS [Delivery Person]
FROM OPENJSON(@json, '$.recordAttributes') 
WHERE JSON_VALUE(value, '$.fieldName') = 'Delivery Person'

Which gives me the result I’m after.

My Solution as a Function

Because I needed to query the JSON attributes on many different records I set up this Function to use in my queries…

CREATE FUNCTION dbo.JSON_GetFieldValueFromJSONArray
	(
		@jsonArry NVARCHAR(MAX),
		@path AS NVARCHAR(1000),
		@valueField NVARCHAR(255),
		@searchField NVARCHAR(255),
		@searchValue NVARCHAR(255)
	)
RETURNS NVARCHAR(1000)
AS
BEGIN
	DECLARE @Result NVARCHAR(1000)

	SELECT @Result = JSON_VALUE(Value, @valueField) 
	FROM OPENJSON(@jsonArry, @path) 
	WHERE JSON_VALUE(value, @searchField) = @searchValue

	RETURN @Result
END
GO

• @path is the path to the array of key value pairs, in the example JSON it is '$.recordAttributes'.
• @valueField is the key name for the value you want returned.
• @searchField is the key that you want to search on and @searchValue is the value you are looking for.

Troubleshooting

If you get an error like JSON path is not properly formatted. Unexpected character 'v' is found at position 0. It will either be because your JSON is invalid, or because one of the paths is not valid JSON, it seems that, for the paths, $. is needed at the beginning.

Example

To see a working example of the above function run the code below…

DROP TABLE IF EXISTS dbo.RecordAttributes
GO

CREATE TABLE dbo.RecordAttributes
(
	RecordID INT PRIMARY KEY IDENTITY NOT NULL,
	JsonAttributes NVARCHAR(MAX) NOT NULL
)
GO

INSERT INTO	dbo.RecordAttributes (JsonAttributes)
VALUES
	(N'{"recordAttributes": [{"fieldName": "Delivery Method","value": "In Person","sortOrder": 1}, {"fieldName": "Delivery Person","value": "Jason","sortOrder": 2}]}')
GO

SELECT RecordID
	, dbo.JSON_GetFieldValueFromJSONArray(JsonAttributes, '$.recordAttributes', '$.value', '$.fieldName', 'Delivery Method') AS DeliveryMethod
	, dbo.JSON_GetFieldValueFromJSONArray(JsonAttributes, '$.recordAttributes', '$.value', '$.fieldName', 'Delivery Person') AS DeliveryPerson
	, JsonAttributes
FROM dbo.RecordAttributes