使用OPENJSON將JSON轉換為SQLServer中的逗號分隔串列

在 SQL Server 2016 中，我嘗試轉換以下 JSON：

DECLARE @json NVARCHAR(MAX);

SET @json = 
N' {
        "date": "2021-12-31",
        "distributor_name": "Test",

        "movies": [
            {
                "category_id": 3,
                "name": "Dune",
                "budget": 165,
                "release_date": "2021-09-03",

                "location": [
                    {
                        "location_type": 1, 
                        "location_code": "US"
                    },
                    {
                        "location_type": 2,
                        "location_code": "CA"
                    },
                    {
                        "location_type": 2,
                        "location_code": "UK"
                    }
                ]
            },
            {
                "category_id": 2,
                "name": "No Time to Die",
                "budget": 250,
                "release_date": "2021-09-28",
                
                "location": [
                    {
                        "location_type": 1, 
                        "location_code": "US"
                    },
                    {
                        "location_type": 1, 
                        "location_code": "UK"
                    }
                ]
            }
        ]
    }
';

進入：

什么應該被添加到下面的陳述句，以確保所有location_code與location_type = 1去下country一個逗號分隔的串列格式，以及所有location_code與location_type = 2去下distribution一個逗號分隔的串列格式。

SELECT *
FROM OPENJSON(@json, '$.movies')
  WITH (
    category_id INT '$.category_id',
    name VARCHAR(255) '$.name',
    budget INT '$.budget',
    release_date DATE '$.release_date'
  )

uj5u.com熱心網友回復：

您可以OPENJSON在子查詢中再次使用來拆分內部location陣列。

對于 SQL Server 2016，需要使用FOR XML聚合。

DECLARE @sep varchar(10) = ',';

SELECT
  j.category_id,
  j.name,
  j.budget,
  j.release_date,
  country = STUFF((
      SELECT
        @sep   location_code
      FROM OPENJSON(j.location)
        WITH (
          location_type int,
          location_code char(2)
        ) j2
      WHERE j2.location_type = 1
      FOR XML PATH(''), TYPE
  ).value('text()[1]','varchar(max)'), 1, LEN(@sep), ''),
  distribution = STUFF((
      SELECT
        @sep   location_code
      FROM OPENJSON(j.location)
        WITH (
          location_type int,
          location_code char(2)
        ) j2
      WHERE j2.location_type = 2
      FOR XML PATH(''), TYPE
  ).value('text()[1]','varchar(max)'), 1, LEN(@sep), '')
FROM OPENJSON(@json, '$.movies')
  WITH (
    category_id int,
    name nvarchar(200),
    budget int,
    release_date datetime,
    location nvarchar(max) AS JSON
  ) j;

以后的版本可以STRING_AGG更簡單地使用：

SELECT
  j.category_id,
  j.name,
  j.budget,
  j.release_date,
  country = (
      SELECT STRING_AGG(j2.location_code, ',')
      FROM OPENJSON(j.location)
        WITH (
          location_type int,
          location_code char(2)
        ) j2
      WHERE j2.location_type = 1
  ),
  distribution = (
      SELECT STRING_AGG(j2.location_code, ',')
      FROM OPENJSON(j.location)
        WITH (
          location_type int,
          location_code char(2)
        ) j2
      WHERE j2.location_type = 2
  )
FROM OPENJSON(@json, '$.movies')
  WITH (
    category_id int,
    name nvarchar(200),
    budget int,
    release_date datetime,
    location nvarchar(max) AS JSON
  ) j;

資料庫<>小提琴

標籤：json sql-server 打开-json

上一篇：如何在沒有鍵的情況下提取陣列元素？

下一篇：如何在Stack中使用分析進行構建？