How to Query JSON

• mediacommentry

  Old Hand

  Points: 348

  More actions

  September 18, 2018 at 12:28 am

  #408466

  Hi,

  I have this query SELECT  CA.PartnerBankingAdditionalAttributes
FROM RltView.ActiveAndInactiveMembersPaymentDetails_V  OV WITH (NOLOCK)
CROSS
APPLY (
    SELECT  FisrtName
       ,LastName
       ,IDNumber
       ,Address1
       ,Address2
       ,Address3
       ,FileType
    FROM (VALUES(1))DT(x)
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
   )                CA (PartnerBankingAdditionalAttributes)
WHERE OV.IsActive = 1
AND isPaid = 'Y'

  I want to return data where LastName = 'John'

  I tried this 
  AND JSON_VALUE(LastName, '$.LastName') = 'John'
  but it's giving me errors

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  September 20, 2018 at 7:52 pm

  #2006509

  You don't need to query the JSON, you already have the value in your view, since you're using it to construct the JSON in the first place. 
  
SELECT   CA.PartnerBankingAdditionalAttributes
FROM RltView.ActiveAndInactiveMembersPaymentDetails_V  OV
CROSS
APPLY  (
    SELECT  
FisrtName
        ,LastName
        ,IDNumber
        ,Address1
        ,Address2
        ,Address3
        ,FileType
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )  CA (PartnerBankingAdditionalAttributes)
WHERE OV.IsActive = 1
AND isPaid = 'Y'
AND LastName = 'John