Query a JSON array in SQL

For the purpose of this post, lets not evaluate the db design option and lets focus on the operations on the json column.

Lets say we have a table of customers where we have an Id, a CompanyName and an Address, although these customers can have some information related to a service, for example they may have a specific customer id for each service.

Customer

Id: the id

CompanyName: the company name

Address: the address

--- ServiceId: 1

--- ServiceCustomerId: the customer id for service 1

--- ServiceId: 2

--- ServiceCustomerId: the customer id for service 2

One way we could store this on a single table could be with these columns: Id / Name / Address / ServicesDataInJson

So for this example, the information on the ServicesDataInJson is an array of ‘objects’ that contains the information of our customer on each service. Since there is no native JSON format on SQL Server, the data type of this column is just a nvarchar(max)

Here’s how a simple SELECT looks like:

Now we want to query our customers table by a customer service id (which is inside that json). How can we query this?

SELECT *

FROM Customers c

CROSS APPLY OPENJSON(c.ServicesDataInJson)

WITH (ServiceId int '$.ServiceId',

ServiceCustomerId nvarchar(255) '$.ServiceCustomerId') as jsonValues

WHERE jsonValues.ServiceCustomerId = @TheIdWeWantToSearchFor

The OPENJSON is a table-valued function that parses the json into a row/column result and the WITH clause let us define how we want that output.

Here is the query without the WHERE clause:

Note: The OPENJSON function will not work if the database compatibility level is lower than 130.

To change it:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130