SQL JSON - Get specific values from an array

Question

I'm trying to get 2 specific values from an array within a JSON string along with other values outside the array. So far I have managed to get 1 specific value from the array by using a where clause. But no I'm stuck. I don't know how to get the other value because I can't use 2 where clauses? So maybe someone point me in the right directions?

The array 'identifiers' have several values, but I only want the values where type: code is 1881591 and 1881495.

This is the SQL query I'm using

DECLARE @json NVARCHAR(MAX)
 
SET @json = N'{"response":[{"partyId":"aa343434","prid":"88228822","partyName":"MyCompany",
              "addresses":[{"type":{"code":"1879539","description":"Operating Address"},"addressLine1":"Myaddress 4","addressLine2":"C/O xxx","addressLine3":"","addressLine4":"",
              "city":"yyy","state":{"code":"","description":""},"country":{"code":"1865641","description":"Denmark"},"postalCode":"2222","postalPlusCode":""}],
              "identifiers":[{"type":{"code":"1881600","description":"Bank IBAN"},"value":"888343434"},{"type":{"code":"1881591","description":"DUNS"},"value":"123456789"},{"type":{"code":"1881495","description":"NAT ID - DENMARK CVR NUMBER"},"value":"12345678"}],
              "sourceSystem":"source"},{"partyId":"aa343434","prid":"88228822","partyName":"MyNewCompany",
              "addresses":[{"type":{"code":"1879539","description":"Operating Address"},"addressLine1":"MyOtheraddress 23","addressLine2":"C/O zzz","addressLine3":"","addressLine4":"",
              "city":"zzz","state":{"code":"","description":""},"country":{"code":"1865641","description":"Denmark"},"postalCode":"3333","postalPlusCode":""}],
              "identifiers":[{"type":{"code":"1881591","description":"DUNS"},"value":"987654321"},{"type":{"code":"1881495","description":"NAT ID - DENMARK CVR NUMBER"},"value":"87654321"},
              {"type":{"code":"1881590","description":"CESID"},"value":"22334466"}],
              "sourceSystem":"source"}]}'

SELECT partyname, DUNS, addressLine1, addressLine2, city, country, postalcode FROM OPENJSON(@json, 'strict $.response') 
WITH (
      partyName NVARCHAR(255),
      addresses NVARCHAR(MAX) AS JSON,
      identifiers NVARCHAR(MAX) AS JSON
     )
CROSS APPLY OPENJSON(addresses)
WITH (
      addressLine1 NVARCHAR(255),
      addressLine2 NVARCHAR(255),
      city NVARCHAR(255),
      country NVARCHAR(255) 'strict $.country.description',
      postalCode NVARCHAR(255)
     )
CROSS APPLY OPENJSON(identifiers)
WITH (
      typecode NVARCHAR(255) 'strict $.type.code',
      DUNS NVARCHAR(255) 'strict $.value'
     )
WHERE typecode = '1881591'

This will give me the following result:

But what I'm looking for is:

Best regards Ole

lptr · Accepted Answer · 2021-01-26 11:56:37Z

SELECT partyname, tps.DUNS, tps.CVR, addressLine1, addressLine2, city, country, postalcode 
FROM OPENJSON(@json, 'strict $.response') 
WITH (
      partyName NVARCHAR(255),
      addresses NVARCHAR(MAX) AS JSON,
      identifiers NVARCHAR(MAX) AS JSON
     )
CROSS APPLY OPENJSON(addresses)
WITH (
      addressLine1 NVARCHAR(255),
      addressLine2 NVARCHAR(255),
      city NVARCHAR(255),
      country NVARCHAR(255) 'strict $.country.description',
      postalCode NVARCHAR(255)
     )
CROSS APPLY
(
    SELECT 
        MAX(CASE WHEN id.typedescription = 'DUNS' THEN id.typevalue END) AS DUNS,
        MAX(CASE WHEN id.typedescription = 'NAT ID - DENMARK CVR NUMBER' THEN id.typevalue END) AS CVR
    FROM OPENJSON(identifiers)
    WITH 
    (
          typecode NVARCHAR(255) 'strict $.type.code',
          typedescription NVARCHAR(255) 'strict $.type.description',
          typevalue NVARCHAR(255) 'strict $.value'
    ) AS id    
) AS tps

Gordon Linoff · Accepted Answer · 2021-01-26 11:58:59Z

If I understand correctly, you can use conditional aggregation to move these to separate columns:

SELECT partyname, \addressLine1, addressLine2, city, country, postalcode, d.*
FROM OPENJSON(@json, 'strict $.response') 
WITH (
      partyName NVARCHAR(255),
      addresses NVARCHAR(MAX) AS JSON,
      identifiers NVARCHAR(MAX) AS JSON
     )
CROSS APPLY OPENJSON(addresses)
WITH (
      addressLine1 NVARCHAR(255),
      addressLine2 NVARCHAR(255),
      city NVARCHAR(255),
      country NVARCHAR(255) 'strict $.country.description',
      postalCode NVARCHAR(255)
     )
CROSS APPLY
    (SELECT MAX(CASE WHEN typecode = '1881591' THEN DUNS END) duns_1881591,
            MAX(CASE WHEN typecode = '1881495' THEN DUNS END) duns_1881495
     FROM OPENJSON(identifiers)
          WITH (typecode NVARCHAR(255) 'strict $.type.code',
                DUNS NVARCHAR(255) 'strict $.value'
               )
      WHERE typecode IN ( '1881591', '1881495')
     ) d

Here is a db<>fiddle.

Collectives™ on Stack Overflow

SQL JSON - Get specific values from an array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related