t-sql openjson update each elem of array

Question

I try to insert order information and order lines with open json.

Here is example json.

declare @json nvarchar(max) = '
{"OrderID":0,
"CustomerID":2250,
"SupplierID":1,
"CuratorID":988,
"FirmID":null,
"OrderStateID":2,
"DeliveryTypeID":1,
"Code1C":"",
"CreationDate":"2017-11-12T01:05:14.3233187+03:00",
"ReservationDate":"2017-11-15T01:05:16.1306759+03:00",
"AssemblyDate":null,
"DeliveryDate":"2017-11-12T01:05:15.1748244+03:00",
"TransportAddress":"",
"TransportName":"","TransportPhone":"","RecieverAddress":"","RecieverName":"",
"RecieverPhone":"","Comment":"","LoaderID":null,"DriverID":null,
"CanDelete":true,"LoadingDate":null,"RealizeNum":"","IsDocumentsCreate":false,"DeliveryType":null,"Firm":null,
"OrderLines":[
    {"OrderLineID":0,"OrderID":0,"ProductID":1,"ProductCount":100,"ChangeCount":0,"Price":363.0,"Product":null,"Order":null},
    {"OrderLineID":0,"OrderID":0,"ProductID":3,"ProductCount":100,"ChangeCount":0,"Price":860.0,"Product":null,"Order":null}
]}'

Now I insert main info in Orders table.

insert into Sales.Orders (CustomerID
                        ,SupplierID
                        ,CuratorID
                        ,OrderStateID
                        ,FirmID
                        ,DeliveryTypeID
                        ,Code1C
                        ,CreationDate
                        ,ReservationDate
                        ,DeliveryDate) select * from openjson(@s) with 
    (CustomerID int, SupplierID int, 
    CuratorID int, OrderStateID int, FirmID int, DeliveryTypeID int, Code1C nvarchar(10), 
    CreationDate datetime2, ReservationDate datetime2, DeliveryDate datetime2);
select * from openjson(@s);

It works fine. The next step I need to insert order lines array. But before insert I need to set OrderID propery to each order line.

Here I select last inserted order identity.

declare @ident int;
select @ident = SCOPE_IDENTITY();

But I don't understand, how to set this value to each order line. The next code doesn't work

set @json = JSON_MODIFY(@s, '$.OrderLines.OrderID', @ident);

Thanks for any advices

Answer 1

You can also use set based logic to do this rather than while loop all in SQL. You need to use STRING_AGG instead of FOR JSON PATH/AUTO since there is no root to the objects when you combine them back together.

DECLARE
    @json nvarchar(max) = N'{
"SomeOtherJson": "bleh",
"OrderLines":[
    {"OrderLineID":0,"OrderID":0,"ProductID":1,"ProductCount":100,"ChangeCount":0,"Price":363.0,"Product":null,"Order":null},
    {"OrderLineID":0,"OrderID":0,"ProductID":3,"ProductCount":100,"ChangeCount":0,"Price":860.0,"Product":null,"Order":null}
]}',
    @Id INT = 1;

WITH OrderLines AS (
    SELECT JSON_MODIFY(t.[value], '$.OrderID', @Id) OrderLine, @Id Id
    FROM OPENJSON((SELECT JSON_QUERY(@json, 'lax $.OrderLines'))) t
), OrderLineList AS (
    SELECT CONCAT('[',STRING_AGG(t.OrderLine, ','),']') OrderLines
    FROM OrderLines t
    GROUP BY t.Id
)
SELECT JSON_MODIFY(@json, '$.OrderLines', JSON_QUERY(t.OrderLines))
FROM OrderLineList t

Which results in (beautified):

{
    "SomeOtherJson": "bleh",
    "OrderLines": [
        {
            "OrderLineID": 0,
            "OrderID": 1,
            "ProductID": 1,
            "ProductCount": 100,
            "ChangeCount": 0,
            "Price": 363,
            "Product": null,
            "Order": null
        },
        {
            "OrderLineID": 0,
            "OrderID": 1,
            "ProductID": 3,
            "ProductCount": 100,
            "ChangeCount": 0,
            "Price": 860,
            "Product": null,
            "Order": null
        }
    ]
}

Answer 2

Since OrderLines is an array, you need to reference each line separately.

In this case, you would need to have two separate statements for each object in the array:

set @json = JSON_MODIFY(@json, '$.OrderLines[0].OrderID', @ident);
set @json = JSON_MODIFY(@json, '$.OrderLines[1].OrderID', @ident);

Good reading on how to reference the various elements in JSON with t-SQL is https://www.codeproject.com/Articles/1125457/Native-JSON-Support-in-SQL-Server.

To find out the number of lines you can use:

SELECT count(*)
FROM OPENJSON(@json, '$.OrderLines') 

Then you can use a cursor to loop through the items.

for example:

declare @count      int;
declare @index      int = 0;
declare @sql        nvarchar(1000);
declare @parmasDef  nvarchar(1000);

select @count = count(*)
from OPENJSON(@json, '$.OrderLines');

set @parmasDef = N'@json nvarchar(max) output,@ident int'

while @index < @count
begin
    set @sql = N'set @json = JSON_MODIFY(@json, ''$.OrderLines[' + cast(@index as nvarchar(10)) + '].OrderID'', @ident);'
    exec sp_executesql @sql, @parmasDef, @json = @json output, @ident=@ident
    set @index += 1;
end 

print @json

Answer 3

Not really an answer to what was asked, but to extend the answer of @Jon49, set-based operations can also be applied on a table with a VARCHAR(MAX) column as JSON by using CROSS APPLY. For example:

DECLARE @orders TABLE(Id int, ORDERLINES NVARCHAR(MAX));
INSERT INTO @orders (Id, ORDERLINES) VALUES (1, N'{
"OrderLines":[
    {"OrderLineID":1,"OrderID":0,"ProductID":1,"ProductCount":100,"ChangeCount":0,"Price":363.0,"Product":null,"Order":null},
    {"OrderLineID":2,"OrderID":0,"ProductID":3,"ProductCount":100,"ChangeCount":0,"Price":860.0,"Product":null,"Order":null}
]}')

INSERT INTO @orders (Id, ORDERLINES) VALUES (2, N'{
"OrderLines":[
    {"OrderLineID":3,"OrderID":0,"ProductID":1,"ProductCount":22,"ChangeCount":0,"Price":363.0,"Product":null,"Order":null},
    {"OrderLineID":4,"OrderID":0,"ProductID":3,"ProductCount":33,"ChangeCount":0,"Price":860.0,"Product":null,"Order":null}
]}');

WITH OrderLines AS (
    SELECT JSON_MODIFY(t.[value], '$.OrderID', o.Id) OrderLine, o.Id
    FROM @orders o
    CROSS APPLY OPENJSON((SELECT JSON_QUERY(o.ORDERLINES, 'lax $.OrderLines'))) t
), OrderLineList AS (
    SELECT CONCAT('[',STRING_AGG(t.OrderLine, ','),']') OrderLines, t.Id
    FROM OrderLines t
    GROUP BY t.Id
)

SELECT JSON_MODIFY(o.ORDERLINES, '$.OrderLines', JSON_QUERY(t.OrderLines))
FROM OrderLineList t
INNER JOIN @orders o ON (o.Id=t.Id);