Convert JSON to comma-separated list in SQL Server with OPENJSON

Question

In SQL Server 2016, I am trying to convert the following 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"
                    }
                ]
            }
        ]
    }
';

into:

category_id	name	budget	release_date	country	distribution
3	Dune	165	2021-09-03	US	CA, UK
2	No Time to Die	250	2021-09-28	US, UK	NULL

What should be added to the following statement to ensure that all location_code with location_type = 1 go under country in a comma-separated list format, and all location_code with location_type = 2 go under distribution in a comma-separated list format.

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

Answer 1

You can use OPENJSON a second time in a subquery to break out the inner location array.

For SQL Server 2016, you need to use FOR XML to aggregate.

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;

Later versions can use STRING_AGG much more simply:

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;

db<>fiddle