SQL Server - объект JSON содержит массив внутри массива

Используйте ваш подход с OPENJSON(), AS JSON и CROSS APPLY, просто не создавайте временные таблицы:

DECLARE @json nvarchar(max)
SET @json = N'{... your JSON here ...}'

SELECT
   DENSE_RANK() OVER (ORDER BY json.productId) Rn,
   json.productId Product,
   color.attrValue Color, 
   size.attrValue Size
FROM OPENJSON(@json)
WITH (
   productId nvarchar(max) '$.productId',
   itemInfo nvarchar(max) '$.itemInfo' AS JSON
) json
CROSS APPLY (
   SELECT *
   FROM OPENJSON(json.itemInfo)
   WITH (
      attributes nvarchar(max) '$.attributes' AS JSON
   )
) item
CROSS APPLY (
   SELECT *
   FROM OPENJSON(item.attributes)
   WITH (
      attrName nvarchar(max) '$.attrName',
      attrValue nvarchar(max) '$.attrValue'
   )
   WHERE attrName = 'Color'
) color
CROSS APPLY (
   SELECT *
   FROM OPENJSON(item.attributes)
   WITH (
      attrName nvarchar(max) '$.attrName',
      attrValue nvarchar(max) '$.attrValue'
   )
   WHERE attrName = 'Size'
) size

Я думаю, вы можете попробовать (JSON PARSER WITH PIVOT SQL), как этот запрос:

DECLARE @json   NVARCHAR(MAX) = 
'{
    "productId": "508634800",
    "description": "Generic description",
    "itemInfo": [
        {
            "sku": "4645309",
            "attributes": [
                {
                    "attrName": "Size",
                    "attrValue": "M"
                },
                {
                    "attrName": "Color",
                    "attrValue": "Burgundy"
                }
            ]
        },
        {
            "sku": "4645318",
            "attributes": [
                {
                    "attrName": "Size",
                    "attrValue": "XL"
                },
                {
                    "attrName": "Color",
                    "attrValue": "Burgundy"
                }
            ]
        },
            {
            "sku": "4645400",
            "attributes": [
                {
                    "attrName": "Size",
                    "attrValue": "L"
                },
                {
                    "attrName": "Color",
                    "attrValue": "White"
                }
            ]
        }
    ]
}'




SELECT /*sku,*/ Size, Color
FROM 
(
SELECT productId, sku, attrName, attrValue
FROM
    OPENJSON(@json)
WITH ( productId  int 'strict $.productId',  
description  NVARCHAR(MAX) '$.description',
        itemInfos   NVARCHAR(MAX) '$.itemInfo' AS JSON
    ) 
     outer apply openjson( itemInfos ) 
                     with ( sku nvarchar(8) '$.sku' ,
                            attributes  NVARCHAR(MAX) '$.attributes' AS JSON    )
                    outer apply openjson( attributes ) 
                         with ( attrName nvarchar(MAX) '$.attrName' ,
                                attrValue   NVARCHAR(MAX) '$.attrValue' )
) AS SUBJSONQUERY
PIVOT(
    MAX(SUBJSONQUERY.attrValue) FOR SUBJSONQUERY.attrName IN (Size,Color) ) AS pvtSubJsonQuery

РЕЗУЛЬТАТ:

Size    Color
M        Burgundy
XL       Burgundy
L        White

БД Fiddle здесь: https: // dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=4bdd519517b86f9321f6b12e78eddf18