Парсинг вложенного JSON без заголовков в базу данных SQL

У меня есть вложённый JSON без заголовков, и я не могу распарсить его с помощью SQL. Я пытаюсь загрузить эти данные в таблицу SQL.

Вот пример данных

[
    [ 123, 1123, "94.05", 
      [ 
        [ 11234, 31040, "100.00",
          [ 
            [ 112345, 252783, 20, "Pass", "YES"],       
            [ 61169237, 252785, 5, "Pass", "YES" ],
            [ 61169238, 252788, 5, "Pass", "YES" ]
          ]
        ]
      ]
    ]
]

Вот мой запрос. Я умею распарсить первую часть JSON, но не вложенные. Как мне ссылаться на вложённые JSON, если у них нет заголовков?

SELECT 
    m.[udAuditID], m.[assessmentID],
    m.category_json,
    s.CatID, s.CategoryID,
    s.Score, s.SecID, s.SectionID
FROM 
    [dbo].auditfinal m
CROSS APPLY 
    OPENJSON(m.category_json) 
        WITH (
             CatID BIGINT '$[0]',
             CategoryID BIGINT '$[1]',
             Score VARCHAR(max) '$[2]',
             SecID BIGINT '$[0][0]',
             SectionID BIGINT '$[0][1]'
             ) s

Как вы можете видеть на приведенных ниже результатах, я могу распарсить первый набор, показанный жёлтым, но не дальше этого.

введите описание изображения здесь

Любая помощь или предложение будут очень признательны!

Если ваш JSON содержит вложенные блоки / массивы, вы можете продолжать добавлять подзапросы APPLY до тех пор, пока не доберётесь до самого низа.

Если ваша схема JSON статична, вы, вероятно, можете составить фиксированный запрос, который даст вам все, что вам нужно. Что-то вроде этого:

-- Пример JSON-значения
declare @v nvarchar(max) = N'[
    [
        123,
        1123,
        "94.05",
        [
            [
                11234,
                31040,
                "100.00",
                [
                    [ 112345, 252783, 20, "Pass", "YES" ],
                    [ 61169237, 252785, 5, "Pass", "YES" ],
                    [ 61169238, 252788, 5, "Pass", "YES" ]
                ]
            ]
        ]
    ]
]';

select *
from openjson(@v, '$[0]') r
    outer apply (
        select a.[key] as [Key2], a.value as [V2], a.type as [Type2]
        from openjson(r.value, '$[0]') a
        where r.type = 4
    ) l1
    outer apply (
        select b.[key] as [Key3], b.value as [V3], b.type as [Type3]
        from openjson(l1.V2, '$') b
        where l1.Type2 = 4
    ) l2
    outer apply (
        select c.[key] as [Key4], c.value as [V4], c.type as [Type4]
        from openjson(l2.V3, '$') c
        where l2.Type3 = 4
    ) l3;

Чтобы избежать проблем, когда OPENJSON пытается распарсить не-JSON значение, каждое из этих вложенных подзапросов имеет фильтры type = 4, так что будут пытаться распарсить только JSON объекты, а не скалярные значения.

Если ваша структура JSON динамична и непредсказуема, вам понадобится какой-то обобщённый иерархический парсер. Пример ниже можно использовать как отправную точку. Просто не ожидайте, что это будет быстро.

-- На основе той же переменной, что и выше
with cte as (
    select cast(r.[key] as varchar(max)) as [AxisPath], r.value as [Value],
        r.type as [ValueType]
    from openjson(@v, '$') r
    union all
    select c.AxisPath + '.' + cast(d.[key] as varchar(max)), d.value,
        d.type
    from cte c
        cross apply openjson(c.Value, '$') d
    where c.ValueType = 4 -- Только JSON объекты
)
select *
from cte c
-- В конце не должно остаться нераспарсенного JSON
where c.ValueType != 4
order by c.AxisPath;