CODEWITHSUNDEEP
sql-serverazure-sql-database
Peter
Peter

Reputation: 99

T-SQL JSON: How do i search for a value in a JSON array

We are using Azure SQL - and have a table called companies where one of the columns contains JSON. The structure for the JSON field is:

{
  "DepartmentLink": "https://company.com",
  "ContactName": "John Doe",
  "ContactTitle": "CEO",
  "ContactEmail": "[email protected]",
  "ContactPhone": "xx xx xx xx xx",
  "ContactImage": "https://company.com/xyz.jpg",
  "ZipCodes": [
    "7000",
    "7007",
    "7017",
    "7018",
    "7029"
  ]
}

The structure for the Table looks like

[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NULL,
[JsonString] [nvarchar](max) NULL,
-- other fields --

where [JsonString] has this structure:

{
  "DepartmentLink": "https://company.com",
  "ContactName": "John Doe",
  "ContactTitle": "CEO",
  "ContactEmail": "[email protected]",
  "ContactPhone": "xx xx xx xx xx",
  "ContactImage": "https://company.com/xyz.jpg",
  "ZipCodes": [
    "7000",
    "7007",
    "7017",
    "7018",
    "7029"
  ]
}

Given a ZipCode .e.g. 7018 I need to find the company which has this ZipCode in the Json Array ZipCodes - and return elements from the record (these elements are all present as ordinary "fields" - so I do not need to return the JSON.).

I'm having problems finding out how to do this. Any suggestions? I'm quite new to JSON in SQL.

Upvotes: 3

Views: 8801

Answers (2)

wBob
wBob

Reputation: 14389

use OPENJSON and CROSS APPLY to shred the zip codes array then add a WHERE clause with your filter, something like this:

SELECT *, JSON_VALUE( yourJSON, '$.DepartmentLink' ) AS DepartmentLink
FROM dbo.Companies 
    CROSS APPLY OPENJSON( yourJSON, '$.ZipCodes') x
WHERE x.value = '7000';

Upvotes: 6

Peter
Peter

Reputation: 99

It tried this and it seems to work. Is this a recommendable way to do it?

SELECT *
FROM [dbo].[Companies]
WHERE EXISTS (
      Select *
      FROM OPENJSON(JsonString,'$.ZipCodes') 
      WHERE Value = '7018'
)

Upvotes: 3

Related Questions