question

CarltonPatterson-0401 avatar image
0 Votes"
CarltonPatterson-0401 asked MelissaMa-msft edited

How to Query Arrays with T-SQL

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":"taqsegzjuixulfrymtxptsxxuucoqsjxxlxp@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":"soeligorbdrxsdikzjkcswkauhmghnifimhc@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

102736-probatedata.png




Thanks

sql-server-transact-sql
probatedata.png (10.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered CarltonPatterson-0401 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Erland,

I knew you would come through ... I'm going to check it out now.

Thanks

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @CarltonPatterson-0401

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":"soeligorbdrxsdikzjkcswkauhmghnifimhc@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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Wow, Melissa, this is amazing, thank you

0 Votes 0 ·

Hi @CarltonPatterson-0401

Thanks for your update and confirmation.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·