Azure 数据工厂复杂 JSON 源(嵌套数组)到 Azure Sql 数据库？

Question

我有一个 JSON 源文档，将定期上传到 Azure Blob 存储。客户希望使用 Azure 数据工厂将此输入写入 Azure Sql 数据库。然而，JSON 很复杂，有许多嵌套数组，到目前为止我还没有找到一种方法来扁平化文档。也许这不受支持/不可能？

[
{
"ActivityId": 1,
    "Header": {},
    "Body": [{
        "1stSubArray": [{
            "Id": 456,
            "2ndSubArray": [{
                "Id": "abc",
                "Descript": "text",
                "3rdSubArray": [{
                    "Id": "def",
                    "morefields": "text"
                },
                {
                    "Id": "ghi",
                    "morefields": "sample"
                }]
            }]
        }]
    }]
}
]

我需要将其压平:

ActivityId, Id, Id, Descript, Id, morefields
1, 456, abc, text1, def, text
1, 456, abc, text2, ghi, sample
1, 456, xyz, text3, jkl, textother
1, 456, xyz, text4, mno, moretext 

每个 ActivityId 可能有 8 个以上的平面记录。有人看到过这种情况并找到了使用 Azure 数据工厂复制数据解决问题的方法吗？

Answer 1

Azure SQL 数据库具有一些强大的 JSON 粉碎功能，包括粉碎 JSON 的 OPENJSON 和从 JSON 返回标量值的 JSON_VALUE。由于您的架构中已经有 Azure SQL DB，因此使用它比添加其他组件更有意义。

那么为什么不采用 ELT 模式，使用数据工厂将 JSON 插入到 Azure SQL DB 中的表中，然后调用存储过程任务来分解它呢？一些基于您的示例的示例 SQL:

DECLARE @json NVARCHAR(MAX) = '[
{
  "ActivityId": 1,
  "Header": {},
  "Body": [
    {
      "1stSubArray": [
        {
          "Id": 456,
          "2ndSubArray": [
            {
              "Id": "abc",
              "Descript": "text",
              "3rdSubArray": [
                {
                  "Id": "def",
                  "morefields": "text"
                },
                {
                  "Id": "ghi",
                  "morefields": "sample"
                }
              ]
            },
            {
              "Id": "xyz",
              "Descript": "text",
              "3rdSubArray": [
                {
                  "Id": "jkl",
                  "morefields": "textother"
                },
                {
                  "Id": "mno",
                  "morefields": "moretext"
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}
]'

--SELECT @json j

-- INSERT INTO yourTable ( ...
SELECT
    JSON_VALUE ( j.[value], '$.ActivityId' ) AS ActivityId,
    JSON_VALUE ( a1.[value], '$.Id' ) AS Id1,
    JSON_VALUE ( a2.[value], '$.Id' ) AS Id2,
    JSON_VALUE ( a2.[value], '$.Descript' ) AS Descript,
    JSON_VALUE ( a3.[value], '$.Id' ) AS Id3,
    JSON_VALUE ( a3.[value], '$.morefields' ) AS morefields

FROM OPENJSON( @json ) j
    CROSS APPLY OPENJSON ( j.[value], '$."Body"' ) AS m
        CROSS APPLY OPENJSON ( m.[value], '$."1stSubArray"' ) AS a1
            CROSS APPLY OPENJSON ( a1.[value], '$."2ndSubArray"' ) AS a2
                CROSS APPLY OPENJSON ( a2.[value], '$."3rdSubArray"' ) AS a3;

如您所见，我使用了CROSS APPLY 来导航多个级别。我的结果: