Update properties in SQL Json fields with nested objects

Question

I'm new to JSON methods in SQL

I'm working with a huge JSON field, which has such a structure :

{
    "A": 1,
    "B": 2,
    "C": 0,
    "data": [
        {
          "id": "id_i_want",
          "value": "[{
                "prop1":7,
                "prop2":"X",
                "prop3":"4",
                "passages":[
                    {
                        "id":0,
                        "number":"E24",
                        "date":"11/12/2019"
                    },
                    {
                        "id":0,
                        "number":"F28",
                        "date":"11/11/2019"
                    },
                    {
                        ...
                    }
                ]
            }]
        },
        {
          "id": "id_i_do_NOT_want",
          "value": Same structure as above
        }
    ]
}

This JSON fields is stored in a nvarchar(MAX) field in SQLServer.

So the JSON has a property data, which contains a list of elements.
These elements have a value property, which contains a list of passages.
All the passages currently have id = 0

What I need to do :

I would like ton increment all the id of passages, starting from 1, but only the ones in the object which has the ID id_i_want, and NOT the others.

How can I do that with a SQL script ?
I tried to follow this post, but without success

Any help appreciated

@Charlieface `data` only have one property `id`, and one property `value`. Always. However, `value` may have multiple items, and each `value` may have multiple `passages` — user2687153, Aug 10 '21 at 16:05

score 1 · Accepted Answer · answered Aug 10 '21 at 16:24

First change the Json data to tabular data, then update the table, and then convert the table back to Json.

I have prepared the following code, you can use it easily with a little change.

declare @varData nvarchar(max) = '{
    "A": 1,
    "B": 2,
    "C": 0,
    "data": [
        {
          "id": "id_i_want",
          "value": [{
                "prop1":7,
                "prop2":"X",
                "prop3":"4",
                "passages":[
                    {
                        "id":0,
                        "number":"E24",
                        "date":"11/12/2019"
                    },
                    {
                        "id":0,
                        "number":"F28",
                        "date":"11/11/2019"
                    }
                ]
            }]
        },
        {
          "id": "id_i_do_NOT_want"
        }
    ]
}';

DECLARE @jsontable TABLE (A varchar(5), b varchar(5), c varchar(5),id NVARCHAR(50),prop1 int,prop2 varchar(5),prop3 varchar(5),mid int ,number varchar(5),date date);
DECLARE @maintable TABLE (A varchar(5), b varchar(5), c varchar(5),id NVARCHAR(50),prop1 int,prop2 varchar(5),prop3 varchar(5),mid int ,number varchar(5),date date);


insert into @jsontable
SELECT A,b,C,id,prop1,prop2,prop3,mid,number,date
FROM OPENJSON(@varData)
  WITH (
    A varchar(5) '$.A',
    B varchar(5) '$.B',
    C varchar(5) '$.C',
    jdata NVARCHAR(MAX) '$.data' AS JSON
  )
  OUTER APPLY OPENJSON(jdata)
  WITH (
    id NVARCHAR(50) '$.id',
    jvalue NVARCHAR(MAX) '$.value' AS JSON
   )
   OUTER APPLY OPENJSON(jvalue)
  WITH (
    prop1 int '$.prop1',
    prop2 varchar(5) '$.prop2',
    prop3 varchar(5) '$.prop3',
    jpassages NVARCHAR(MAX) '$.passages' AS JSON
   )
   OUTER APPLY OPENJSON(jpassages)
  WITH (
     mid int '$.id',
     number varchar(5) '$.number',
     date date '$.date'
   )
where id = 'id_i_want'
   



Declare @A varchar(5)
Declare @b varchar(5)
Declare @c varchar(5)
Declare @id NVARCHAR(50)
Declare @prop1 int
Declare @prop2 varchar(5)
Declare @prop3 varchar(5)
Declare @mid int = 0
Declare @number varchar(5)
Declare @date date



While((Select Count(*) From  @jsontable)>0)
Begin

set @mid = @mid + 1
Set @A=(Select Top 1 A From @jsontable)
Set @B=(Select Top 1 B From @jsontable)
Set @C=(Select Top 1 C From @jsontable)
Set @id=(Select Top 1 id From @jsontable)
Set @prop1=(Select Top 1 prop1 From @jsontable)
Set @prop2=(Select Top 1 prop2 From @jsontable)
Set @prop3=(Select Top 1 prop3 From @jsontable)
Set @number=(Select Top 1 number From @jsontable)
Set @date=(Select Top 1 date From @jsontable)

insert into @maintable values
(@A,@B,@C,@id,@prop1,@prop2,@prop3,@mid,@number,@date)


Delete @jsontable Where A=@A and B = @B and C = @C and id = @id and prop1 = @prop1 
and prop2 = @prop2 and prop3 = @prop3 and number = @number and date = @date

End

select * from @maintable

demo in db<>fiddle

Thanks a lot, i'll try to adapt it but it seems working! – user2687153 Aug 13 '21 at 14:43 — user2687153, Aug 13 '21 at 14:43

Update properties in SQL Json fields with nested objects

1 Answers1