How to Query Arrays with T-SQL

Question

Post reply

How to Query Arrays with T-SQL

carlton 84646

SSC Eights!

Points: 902

June 6, 2021 at 8:42 pm

#3892681

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

probatedata

Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18484 More actions · Answer 1

This is JSON:

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

eg

SELECT JSON_VALUE(ce_data, '$.declaration')
,JSON_VALUE(ce_data, '$.applicationType')
,JSON_VALUE(ce_data, '$.deceasedAddress')
,JSON_VALUE(ce_data, '$.boDocumentsUploaded')
,JSON_VALUE(ce_data, '$.caseType')
,JSON_VALUE(ce_data, '$.ihtReferenceNumber')
,JSON_VALUE(ce_data, '$.primaryApplicantEmailAddress')
--etc
FROM #tmpTable;

nramesh Newbie Points: 2 More actions · Answer 2

select t.ce_data

from #tmpTable t

cross apply openjson(t.ce_data) with (applicationSubmittedDate date, applicationType varchar(50)) c

where c.applicationSubmittedDate='2021-05-17'

and c.applicationType='Personal'