时间戳

首页 > 解决方案 > SQL JSON - 从数组中获取特定值

sql - SQL JSON - 从数组中获取特定值

问题描述

我正在尝试从 JSON 字符串中的数组以及数组外的其他值中获取 2 个特定值。到目前为止，我已经设法通过使用 where 子句从数组中获取 1 个特定值。但不，我被困住了。我不知道如何获得其他值，因为我不能使用 2 where 子句？所以也许有人指出我正确的方向？

数组“标识符”有几个值，但我只想要 type: code 为 1881591 和 1881495 的值。

这是我正在使用的 SQL 查询

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'

这会给我以下结果： 在此处输入图像描述

但我正在寻找的是： 在此处输入图像描述

最好的问候奥莱

标签： sqljsonsql-server

解决方案

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

推荐阅读