Intento insertar información de pedido y líneas de pedido con json abierto.

Aquí está el ejemplo 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}
]}'

Ahora inserto información principal en la tabla Pedidos.

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

Funciona bien. El siguiente paso que necesito es insertar una matriz de líneas de pedido. Pero antes de insertar, necesito establecer la propiedad OrderID para cada línea de pedido.

Aquí selecciono la última identidad de pedido insertada.

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

Pero no entiendo cómo establecer este valor para cada línea de pedido. El siguiente código no funciona

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

Gracias por cualquier consejo

0
Oleg Volkov 12 nov. 2017 a las 02:10

2 respuestas

La mejor respuesta

Dado que OrderLines es una matriz, debe hacer referencia a cada línea por separado.

En este caso, necesitaría tener dos declaraciones separadas para cada objeto en la matriz:

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

Una buena lectura sobre cómo hacer referencia a los diversos elementos en JSON con t-SQL es https://www.codeproject.com/Articles/1125457/Native-JSON-Support-in-SQL-Server.

Para averiguar la cantidad de líneas que puede utilizar:

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

Luego, puede usar un cursor para recorrer los elementos.

Por ejemplo:

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
0
Chana T 14 nov. 2017 a las 09:30

También puede utilizar la lógica basada en conjuntos para hacer esto en lugar de while loop all en SQL. Necesita usar STRING_AGG en lugar de FOR JSON PATH/AUTO ya que no hay raíz para los objetos cuando los vuelve a combinar.

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

Lo que da como resultado (embellecido):

{
    "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
        }
    ]
}
0
Jon49 21 feb. 2019 a las 22:51