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?
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 |