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

Question

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.

*Now I want to add one more data in That.* So why are you using an update statement then, *and* with the same ID as the one before? You should be using an *insert* statement — g00se, Oct 08 '22 at 10:21
No `insert` statement is for insertion the row, in same record I want to add data in my `columnJson ` column where already I have two data, I wann add one more. — David, Oct 08 '22 at 10:23
Oh right, then you'll have to select, append, *then* update. Or use [this](https://stackoverflow.com/questions/13926239/mysql-append-column-value#13926382) technique — g00se, Oct 08 '22 at 10:30
you need to use one of these https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html — nbk, Oct 08 '22 at 10:50

nbk · Accepted Answer · 2022-10-08T11:52:45.913

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

id	columnJson
rowID1	[[{"id": "someId1", "name": "someNamme2"}, "{"id" : "someId3", "name": "someNamme3"}"], {"id": "someId2", "name": "someNamme2"}]

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

id	columnJson
rowID1	[{"id": "someId1", "name": "someNamme2"}, [{"id": "someId2", "name": "someNamme2"}, "{"id" : "someId3", "name": "someNamme3"}"]]

fiddle

It is adding as another array, Is there any possiblity that it should add in same array. @nbk — David, Oct 09 '22 at 06:51
Make a fiddle and can have a look, you have as I mentioned in my answer to learn how to handle it by yourself, so take the fiddle add data and play with it and the other funtions — nbk, Oct 09 '22 at 07:47
Thanks, I have played with the fiddle, I removed `$[0]` to `$` and worked like charm. Also lots of learning here. Thanks again nbk — David, Oct 09 '22 at 09:18

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

1 Answers1