sqlsql-serveropen-json

How to join parent object with nested array using OpenJson SQL


I've got a JSON data that has nested array. I want to join the inner array values with the parent object's value, which will later be inserted in an associative table. So far I have tried the following, but can't manage to get the right output.

declare @data nvarchar(max)='[
    {
        "Id": "A",
        "AccountIds": [
            1
        ]
    },
    {
        "Id": "B",
        "AccountIds": [
            2,
            3
        ]
    },
    {
        "Id": "C",
        "AccountIds": [
            1,
            2
        ]
    }
]'

select
    [Id],
    [AccountId]
from
    openjson(@data)
with
    ([Id] nvarchar(20) '$.Id')
cross apply
    openjson(@data)
with
    ([AccountId] nvarchar(MAX) '$.AccountIds' as json)


--select
--  a.value as Id, b.value as AccountId
--from
--  openjson(@data, '$.Id') a
--  join openjson(@data, '$.AccountIds') b
--  on a.[key] = b.[key]

The expected output should be something like this:

Id        AccountId
==        =========
A         1
B         2
B         3
C         1
C         2

Could someone please shed some light on how to achieve the expected output?


Solution

  • Don't join, just create a column with the accountIds as JSON and cross apply that further, something like:

    ...
    select
        [Id],
        y.value as AccountId
    from
        openjson(@data)
    with
        ([Id] nvarchar(20) '$.Id', AccountIds nvarchar(max) as json)
    cross apply
        openjson(accountids) y
    

    Output:

    Id AccountId
    A 1
    B 2
    B 3
    C 1
    C 2