json - Azure Synapse Analytics Json 扁平化

Question

我是 Azure Synapse 的新手，目前遇到以下问题:

我得到一个如下所示的 json:

{
"2022-02-01":[
    {
      "shiftId": ,
      "employeeId": ,
      "duration": ""
    },
    {
      "shiftId": ,
      "employeeId": ,
      "duration": ""
    }
],
  "2022-02-02": [
    {
      "shiftId": ,
      "employeeId": ,
      "duration": ""
    }
],
"2022-02-03": [
    {
      "shiftId": ,
      "employeeId": ,
      "duration": ""
    },
    {
      "shiftId": ,
      "employeeId": ,
      "duration": ""
    }
],
  "2022-02-4": []
}

现在我想对其进行转换，以便将其显示在 View 中。我已经尝试过将数据流用作文档数组，但出现错误。

“模式推断中检测到格式错误的记录。解析模式:FAILFAST”

我想要这样的东西:

date         shiftId   employeeId   duration
___________|_________|____________|_________
2022-02-01 | 1234    | 345345     | 420
2022-02-01 | 2345    | 345345     | 124
2022-02-02 | 5345    | 123567     | 424
2022-02-03 | 5675    | 987542     | 123
2022-02-03 | 9456    | 234466     | 754

Answer 1

Azure Synapse Analytics，专用 SQL 池实际上非常适合 JSON，支持 OPENJSON 和 JSON_VALUE ，因此您可以仅使用以 JSON 作为参数的存储过程。一个简单的例子:

SELECT
    k.[key] AS [shiftDate],
    JSON_VALUE( d.[value], '$.shiftId' ) shiftId,
    JSON_VALUE( d.[value], '$.employeeId' ) employeeId,
    JSON_VALUE( d.[value], '$.duration' ) duration
FROM OPENJSON( @json, '$' ) k
    CROSS APPLY OPENJSON( k.value, '$' ) d;

完整代码:

DECLARE @json NVARCHAR(MAX) = '{
    "2022-02-01": [
        {
            "shiftId": 1234,
            "employeeId": 345345,
            "duration": 420
        },
        {
            "shiftId": 2345,
            "employeeId": 345345,
            "duration": 124
        }
    ],
    "2022-02-02": [
        {
            "shiftId": 5345,
            "employeeId": 123567,
            "duration": 424
        }
    ],
    "2022-02-03": [
        {
            "shiftId": 5675,
            "employeeId": 987542,
            "duration": 123
        },
        {
            "shiftId": 9456,
            "employeeId": 234466,
            "duration": 754
        }
    ]
}'


SELECT
    k.[key] AS [shiftDate],
    JSON_VALUE( d.[value], '$.shiftId' ) shiftId,
    JSON_VALUE( d.[value], '$.employeeId' ) employeeId,
    JSON_VALUE( d.[value], '$.duration' ) duration
FROM OPENJSON( @json, '$' ) k
    CROSS APPLY OPENJSON( k.value, '$' ) d;

我的结果:

如果您想要更动态的东西，您可以使用 Synapse Notebook 或映射数据流。