SQL Server – Insert Values into Table with Nested Select and 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)