Nested select: insert values into table with two subqueries
If I have understood correctly:




create table AggregationChildren(AggregationId int, AggregationChildrenId int, Position int);
create table Code (CodeID int, Serial nvarchar(20));
insert into Code values
(1, '88962730000000004051'),
(2, '81861400000000020227'),
(3, '81861400000000033191'),
(4, '81861400000000046051'),
(5, '88962730000000016653'),
(6, '81861400000001825849'),
(7, '81861400000001832643'),
(8, '81861400000001841911'),
(9, '81861400000001850803'),
(10, '81861400000001862474'),
(11, '81861400000001874774'),
(12, '81861400000001884159'),
(13, '81861400000001898352'),
(14, '81861400000001904764');
GO



14 rows affected






declare @json nvarchar(max);
set @json = '
{
    "Aggregations": [{
        "Parent": "88962730000000004051",
        "Children": [{
            "Serial": "81861400000000020227",
            "Position": "1"
        }, {
            "Serial": "81861400000000033191",
            "Position": "2"
        }, {
            "Serial": "81861400000000046051",
            "Position": "3"
        }
        ]
    }, {
        "Parent": "88962730000000016653",
        "Children": [{
            "Serial": "81861400000001825849",
            "Position": "1"
        }, {
            "Serial": "81861400000001832643",
            "Position": "2"
        }, {
            "Serial": "81861400000001841911",
            "Position": "3"
        }, {
            "Serial": "81861400000001850803",
            "Position": "4"
        }, {
            "Serial": "81861400000001862474",
            "Position": "5"
        }, {
            "Serial": "81861400000001874774",
            "Position": "6"
        }, {
            "Serial": "81861400000001884159",
            "Position": "7"
        }, {
            "Serial": "81861400000001898352",
            "Position": "8"
        }, {
            "Serial": "81861400000001904764",
            "Position": "9"
        }
        ]
    }]
}
';

  
  
  



>     -- extract json fields
>     ;with x as 
>     (
>     SELECT 
>         Parent, 
>         Serial, 
>         Position
>     FROM
>         OPENJSON (@json, '$.Aggregations')
>         WITH (Parent nvarchar(20) '$.Parent', Children nvarchar(max) AS JSON)
>     CROSS APPLY 
>         OPENJSON (Children)
>         WITH (Serial nvarchar(20), Position int)
>     )
>     INSERT INTO AggregationChildren (AggregationId, AggregationChildrenId, Position)
>     SELECT c1.CodeID, c2.CodeID, Position
>     FROM   x
>     JOIN   Code c1
>     ON     c1.Serial = x.Parent
>     JOIN   Code c2
>     ON     c2.Serial = x.Serial;
>     GO
> 
12 rows affected
> 





SELECT * FROM AggregationChildren;
GO



AggregationId | AggregationChildrenId | Position
------------: | --------------------: | -------:
            1 |                     2 |        1
            1 |                     3 |        2
            1 |                     4 |        3
            5 |                     6 |        1
            5 |                     7 |        2
            5 |                     8 |        3
            5 |                     9 |        4
            5 |                    10 |        5
            5 |                    11 |        6
            5 |                    12 |        7
            5 |                    13 |        8
            5 |                    14 |        9



dbfiddle here