Update JSON object MySQL

Question

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

    ) 
);

Lukasz Szozda · Accepted Answer · 2018-08-14 15:46:39Z

3

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

answered Aug 14, 2018 at 15:46

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update JSON object MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related