0

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)
  • Could you add some sample data? – McNets Feb 28 '18 at 12:09
  • @McNets I think the problem is with the select. I have to 'convert' Parent to CodeId and Serial to CodeId without the need to do the same select three times. – VansFannel Feb 28 '18 at 12:22
  • @McNets Sample data added but I don't think is going to be useful for the question about the selects. – VansFannel Feb 28 '18 at 12:28
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.