How to add another data in my sql column of type JSON.

In my table I have one column of json type null.

so I using this command to update the value.

update myTable 
set columnJson = '[{"id" : "someId1" , "name": "someNamme2"}
                  ,{"id" : "someId2", "name": "someNamme2"}]' 
where id = "rowID1";

this is working fine. and I hve two data.

Now I want to add one more data in That.

I am using same command

update myTable  
  set columnJson = '[{"id" : "someId3", "name": "someNamme3"}]'  
 where id = "rowID1";

But the previous value is getting washed away. Is there anyway I can add n number of values. I am doing this in Java.

CodePudding user response：

You need JSON functions like JSON_ARRAY_APPEND see more functions to maniüulate.

Json needs some special function which have to be learned, we usually recomend nit to use JSON, because in a normalized table you can use all the sql functionality there exists,

JSON needs always a moderate learn effort

update myTable 
set columnJson = '[{"id" : "someId1" , "name": "someNamme2"}
                  ,{"id" : "someId2", "name": "someNamme2"}]' 
where id = "rowID1";

Rows matched: 1  Changed: 1  Warnings: 0

update myTable 
set columnJson = JSON_ARRAY_APPEND(columnJson, '$[0]', '{"id" : "someId3", "name": "someNamme3"}')

Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM myTable

fiddle

And if you want another position you change te point where it shold change

update myTable 
set columnJson = '[{"id" : "someId1" , "name": "someNamme2"}
                  ,{"id" : "someId2", "name": "someNamme2"}]' 
where id = "rowID1";

Rows matched: 1  Changed: 1  Warnings: 0

update myTable 
set columnJson = JSON_ARRAY_APPEND(columnJson, '$[1]', '{"id" : "someId3", "name": "someNamme3"}')

Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM myTable

fiddle