How to Query Arrays with T-SQL

Question

Hello Community,

Can someone let me know how to query Array with T-SQL?

For example, for the sample table below I would like to query the field 'ce_data' to find the following:

where

applicationSubmittedData = 2021-05-17

and

applicationType = personal

and

deceasedDiedEngOrWales = No

The sample data is as follows:

CREATE TABLE #tmpTable (  
    ce_data nvarchar(max))  
  
INSERT #tmpTable VALUES  
(N'{"declaration":{},"applicationSubmittedDate":"2021-05-17","applicationType":"Personal","deceasedAddress":{},"boDocumentsUploaded":[],"caseType":"intestacy"}'),  
(N'{"declaration":{},"applicationSubmittedDate":"2021-05-17","applicationType":"Personal","ihtReferenceNumber":"Not applicable","primaryApplicantEmailAddress":"******@probatetest.com","deceasedAddress":{},"boDocumentsUploaded":[],"caseType":"intestacy","languagePreferenceWelsh":"No"}'),  
(N'{"declaration":{},"applicationSubmittedDate":"2021-05-17","applicationType":"Personal","deceasedAddress":{},"boDocumentsUploaded":[],"caseType":"intestacy"}'),  
(N'{"declaration":{},"applicationSubmittedDate":"2021-05-17","applicationType":"Personal","ihtReferenceNumber":"Not applicable","deceasedForenames":"Deceased First Name","primaryApplicantEmailAddress":"******@probatetest.com","deceasedDiedEngOrWales":"No","deceasedAddress":{"AddressLine1":"test address for deceased line 1","PostTown":"test address for deceased town","AddressLine2":"test address for deceased line 2","PostCode":"postcode","AddressLine3":"test address for deceased line 3"},"deceasedDateOfBirth":"1950-01-01","boDocumentsUploaded":[],"caseType":"intestacy","deceasedForeignDeathCertTranslation":"Yes","languagePreferenceWelsh":"No","deceasedSurname":"Deceased Last Name","deceasedForeignDeathCertInEnglish":"No","deceasedDateOfDeath":"2017-01-01"}')  
  
SELECT * FROM #tmpTable  

If you use the above sample to help me with this question, after creating the table, your field should look something like this

Thanks

Answer 1

In the general case, you don't query arrays in SQL. You store data in a relational way, which means one value per cell.

However, your data is JSON, and SQL Server has support for querying JSON, so you can run this query:

SELECT ce_data
FROM   #tmpTable
WHERE  JSON_VALUE(ce_data, '$.applicationSubmittedDate') = '2021-05-17'
   AND JSON_VALUE(ce_data, '$.applicationType') = 'personal'
   AND JSON_VALUE(ce_data, '$.deceasedDiedEngOrWales') = 'No'

It's not going to be particular efficient to query, you have a large data set. In that case, it may be better to shred the JSON data once into tables that you can index.

Answer 2

Hi @Carlton Patterson ，

JSON_VALUE is applied to SQL Server 2016 (13.x) and later. If you have an old version, you could try with below function mentioned in this forum.

CREATE FUNCTION [dbo].[GetJsonValue](@key varchar(100), @data nvarchar(max))  
RETURNS nvarchar(max)  
AS  
BEGIN  
  DECLARE @keyJson varchar(105) = '"' + @key+ '":'  
  DECLARE @keyIdx int = CHARINDEX(@keyJson, @data)  
  IF @keyIdx = 0 RETURN null  
  
  DECLARE @valueIdx int = @keyIdx + LEN(@keyJson)  
  DECLARE @termIdx int = CHARINDEX('"', @data, @valueIdx)  
  
  IF @termIdx <> 0 BEGIN  
    SET @valueIdx = @valueIdx + 1  
    SET @termIdx = CHARINDEX('"', @data, @valueIdx)  
  
    -- Overcome JSON qoute escape  
    WHILE SUBSTRING(@data, @termIdx-1, 1) = '\'  
    BEGIN  
      SET @termIdx = CHARINDEX('"', @data, @termIdx + 1)  
    END  
  END ELSE BEGIN  
    SET @termIdx = CHARINDEX(',', @data, @valueIdx)  
    IF @termIdx = 0 SET @termIdx = CHARINDEX('}', @data, @valueIdx)  
  END  
  
  IF @termIdx = 0 RETURN null  
  
  -- Replace escapte quote before return value  
  RETURN REPLACE(SUBSTRING(@data, @valueIdx, @termIdx - @valueIdx), '\"', '"')  
END  

Then you could call this function as below:

SELECT ce_data  
FROM   #tmpTable  
WHERE  [dbo].[GetJsonValue]('applicationSubmittedDate', ce_data) = '2021-05-17'  
AND [dbo].[GetJsonValue]('applicationType', ce_data) = 'personal'  
AND [dbo].[GetJsonValue]('deceasedDiedEngOrWales', ce_data) = 'No'  

Output:

ce_data  
{"declaration":{},"applicationSubmittedDate":"2021-05-17","applicationType":"Personal","ihtReferenceNumber":"Not applicable","deceasedForenames":"Deceased First Name","primaryApplicantEmailAddress":"******@probatetest.com","deceasedDiedEngOrWales":"No","deceasedAddress":{"AddressLine1":"test address for deceased line 1","PostTown":"test address for deceased town","AddressLine2":"test address for deceased line 2","PostCode":"postcode","AddressLine3":"test address for deceased line 3"},"deceasedDateOfBirth":"1950-01-01","boDocumentsUploaded":[],"caseType":"intestacy","deceasedForeignDeathCertTranslation":"Yes","languagePreferenceWelsh":"No","deceasedSurname":"Deceased Last Name","deceasedForeignDeathCertInEnglish":"No","deceasedDateOfDeath":"2017-01-01"}  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.