How to Query Arrays with T-SQL

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  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

  

  Thanks

   

• Ken McKelvey

  SSCoach

  Points: 18910

  More actions

  June 7, 2021 at 9:00 am

  #3892881

  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

  June 8, 2021 at 5:31 am

  #3893236

  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'