SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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"
                    }
                ]
            }
        ]
    }
';

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;
  
  
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

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

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