Nested select: insert values into table with two subqueries I'm developing a Stored procedure for SQL Server 2016.

I have to do this (the following sql statement doesn't work):

Insert into AggregationChildren (AggregationId, AggregationChildrenId, Position)
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)


My problem is that AggregationId and AggregationChildrenId are integers and Parent and Serial are nvarchar(20).

There is a relationship between Parent and AggregationId, and between Serial and AggregationChildrenId. I can get it using Code table.

I know how can I do it when I have to insert values for one column:

Insert into AggregationChildren (AggregationId)
    Select CodeId from Code Where Serial = (SELECT Parent
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY 
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int))


The data I'm trying to insert is:

{
    ...,
    "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"
        },
        ...
        ]
    }]
}


But, how can I do it if I have to use two Select CodeId from Code Where Serial = (SELECT ...?

I think I have to do something like this but without doing the same select three times:

 Insert into AggregationChildren (AggregationId, AggregationChildrenId, Position)
    Select CodeId from Code Where Serial = (SELECT Parent
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int)), 
    Select CodeId from Code Where Serial = (SELECT Serial
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int)), 
    SELECT 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)
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