Seleção aninhada: insira valores na tabela com duas subconsultas

Estou desenvolvendo um procedimento armazenado para SQL Server 2016.

Eu tenho que fazer isso (a seguinte instrução sql não funciona):

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)

Meu problema é que AggregationIde AggregationChildrenIdsão inteiros e Parente Serialsão nvarchar(20).

Existe uma relação entre Parente AggregationId, e entre Seriale AggregationChildrenId. Eu posso obtê-lo usando a Codetabela.

Eu sei como posso fazer isso quando tenho que inserir valores para uma coluna:

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

Os dados que estou tentando inserir são:

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

Mas, como posso fazer isso se tiver que usar dois Select CodeId from Code Where Serial = (SELECT ...?

Acho que tenho que fazer algo assim mas sem fazer o mesmo select três vezes:

 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)