MySql Update JSON Value

Question

I have a column in a mysql database called params, this contains json data. See sample below;

{
  "menu_text": 1,
  "menu-meta_description": "My Website",
  "enable_page_title": "0",
  "page_title_heading": "h2"
}

I only want to update the enable_page_title key to 1, for every record in the table. I need to leave all other json values intact.

How can I achieve this?

GMB · Accepted Answer · 2019-10-31 16:49:17Z

1

You can use JSON modification function JSON_SET():

select json_set(js, "$.enable_page_title", 1) new_js from t;

Demo on DB Fiddle:

with t as (
    select '{
        "menu_text": 1,
        "menu-meta_description": "My Website",
        "enable_page_title": "0",
        "page_title_heading": "h2"
    }' js
)
select json_pretty(json_set(js, "$.enable_page_title", 1)) new_js from t;

| new_js                                                                                                                |
| -------------------------------------------- |
| {
  "menu_text": 1,
  "enable_page_title": 1,
  "page_title_heading": "h2",
  "menu-meta_description": "My Website"
} |

edited Oct 31, 2019 at 16:49

answered Oct 31, 2019 at 16:47

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

TheOrdinaryGeek Over a year ago

Thanks! What's the js part for?

GMB Over a year ago

@TheOrdinaryGeek: js is the name of the column that contains your json data. I added a demo to my answer for your reference.

Collectives™ on Stack Overflow

MySql Update JSON Value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related