CODEWITHSUNDEEP
mysqljson
Jordi Huertas
Jordi Huertas

Reputation: 93

Update JSON object MySQL

I want to update a JSON object in MySQL.

TABLE

id (int-11, not_null, auto_inc)
labels (json)

JSON Beautify

[
  {
    "tagname": "FOO",
    "category": "CAT_1",
    "isnew": "no",
    "isdeleted": "no"
  },
  {
    "tagname": "BAR",
    "category": "CAT_2",
    "isnew": "yes",
    "isdeleted": "no"
  }
]

I want to add a new TAG element (JSON OBJECT) next to the existing objects, but without SELECTing first the field and updating all the field with text. I have Google a lot but I can not understand yet the MySQL's JSON treatment. I have just learned how to insert data like this: 

INSERT INTO `table_name`(
    `id` ,
    `labels` 
)
VALUES(
    null ,
    JSON_ARRAY
    (
        JSON_OBJECT
        (
            "tagname", "FOO",
            "category", "CAT_1",
            "isnew", "no",
            "isdeleted", "no"
        ),
        JSON_OBJECT
        (
            "tagname", "BAR",
            "category", "CAT_2",
            "isnew", "yes",
            "isdeleted", "no"
        )

    ) 
);

Upvotes: 2

Views: 1163

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You could use JSON_ARRAY_APPEND:

UPDATE tab
SET labels = JSON_ARRAY_APPEND(labels, '$', 
     JSON_OBJECT
        (
            "tagname", "BARX",
            "category", "CAT_3",
            "isnew", "yes",
            "isdeleted", "no"
        )                         
 )
WHERE ID = 1;

DB-Fiddle.com demo

Upvotes: 3

Related Questions