Использование T-SQL для извлечения из JSON

данные

DECLARE @json NVARCHAR(MAX) = '{

    "tables": [

        {

            "name": "PrimaryResult",

            "columns": [

                {

                    "name": "timestamp",

                    "type": "datetime"

                },

                {

                    "name": "message",

                    "type": "string"

                },

                {

                    "name": "severityLevel",

                    "type": "int"

                },

                {

                    "name": "FriendlySeverityLevel",

                    "type": "string"

                },

                {

                    "name": "CycleId",

                    "type": "dynamic"

                }

            ],

            "rows": [

                ["2024-07-25T12:00:00", "Report Started - User: [email protected] MrmClient: 400600 CcID: 000a000b-0000-111c-0a0b-11111a1111a1", 1, "Information", "000a000b-0000-111c-0a0b-11111a1111a1"],

                ["2024-07-24T12:00:00", "Report Started - User: [email protected] MrmClient: 807231 CcID: 000c000d-0000-11c1-0c0d-11111b11111b", 1, "Information", "000c000d-0000-11c1-0c0d-11111b11111b"]

            ]

        }

    ]

}';

используйте OPENJSON с With следующим образом

SELECT *

FROM OPENJSON(@json, '$.tables[0].rows')

WITH (

    timestamp DATETIME '$[0]',

    message NVARCHAR(MAX) '$[1]',

    severityLevel INT '$[2]',

    FriendlySeverityLevel NVARCHAR(MAX) '$[3]',

    CycleId NVARCHAR(MAX) '$[4]'

);

dbfiddle

Если предоставленное содержимое JSON содержит информацию о нескольких таблицах, возможным подходом является динамический оператор. Идея состоит в том, чтобы сгенерировать оператор, который использует OPENJSON() с соответствующими определениями столбцов для каждой таблицы, используя информацию из частей $.tables[*].columns во входном JSON.

Протестируйте JSON с информацией для двух таблиц:

DECLARE @json nvarchar(max) = '{
    "tables": [
        {
        "name": "PrimaryResult",
        "columns": [
            {"name": "timestamp", "type": "datetime"},
            {"name": "message", "type": "string"},
            {"name": "severityLevel", "type": "int"},
            {"name": "FriendlySeverityLevel", "type": "string"},
            {"name": "CycleId", "type": "dynamic"}
        ],
        "rows": [
            ["2024-07-25T12:00:00", "Report Started - User: [email protected] MrmClient: 400600 CcID: 000a000b-0000-111c-0a0b-11111a1111a1", 1, "Information", "000a000b-0000-111c-0a0b-11111a1111a1"],
            ["2024-07-24T12:00:00", "Report Started - User: [email protected] MrmClient: 807231 CcID: 000c000d-0000-11c1-0c0d-11111b11111b", 1, "Information", "000c000d-0000-11c1-0c0d-11111b11111b"]
        ]
        },
        {
        "name": "SecondaryResult",
        "columns": [
            {"name": "timestamp", "type": "datetime"}
        ], 
        "rows": [
            ["2024-07-25T12:00:00"]
        ]
        } 
    ]
}';

Т-SQL:

-- Dynamic statement
DECLARE @stmt nvarchar(max)
; WITH TablesAndColumnsCTE AS (  
   SELECT t.[key] AS TableIndex, c.*
   FROM OPENJSON(@json, '$.tables') t
   CROSS APPLY (
      SELECT 
         [key] AS ColumnIndex, 
         JSON_VALUE([value], '$.name') AS ColumnName, 
         JSON_VALUE([value], '$.type') AS ColumnType
      FROM OPENJSON(t.[value], '$.columns')
   ) c
), StatementsCTE AS (
   SELECT CONCAT(
      N'SELECT * FROM OPENJSON(@json, ''$.tables[', TableIndex, N'].rows'') WITH (',
      STRING_AGG(
         CONCAT(N'[', ColumnName, N'] varchar(max) ''$[', ColumnIndex, N']'''),
         N','
      ) WITHIN GROUP (ORDER BY ColumnIndex),
      N')'
   ) AS Statement
   FROM TablesAndColumnsCTE
   GROUP BY TableIndex
)
SELECT @stmt = STRING_AGG(Statement, N'; ')
FROM StatementsCTE

-- Statement execution
DECLARE @err int  
EXEC @err = sp_executesql @stmt, N'@json nvarchar(max)', @json
IF @err <> 0 PRINT 'Error'

Сформированное заявление на основе тестовых данных опубликовано ниже. Все столбцы определяются как столбцы nvarchar(max), но вы можете легко включить соответствующее выражение CASE и сопоставить типы данных столбцов (datetime, string, int и dynamic в примере) с соответствующими типами данных SQL Server.

SELECT * 
FROM OPENJSON(@json, '$.tables[0].rows') WITH (
   [timestamp] varchar(max) '$[0]',
   [message] varchar(max) '$[1]',
   [severityLevel] varchar(max) '$[2]',
   [FriendlySeverityLevel] varchar(max) '$[3]',
   [CycleId] varchar(max) '$[4]'
);
SELECT * 
FROM OPENJSON(@json, '$.tables[1].rows') WITH (
   [timestamp] varchar(max) '$[0]'
)