How to Modify JSON Column With Data From Another Column in MySQL Database

January 28, 2022

I have table ratingperiods. Lets say for sake of simplicity that it consists of three columns:

create table ratingperiods
(
    Id              binary(16)                not null            primary key,
    EndDate         date                      not null,
    Settings        json                      null,
);

Settings is filled with text:

{"ProductRating": {}}

and I want to add new property to it to make it look like this:

{"ProductRating": {"EndOfAppealPeriod": "2022-02-11T06:10:00Z"}}

To do so, I want to use data from EndDate column and add to it 4 days and 9 hours.
I wrote query like this:

update ratingperiods
set Settings =
        JSON_SET(`Settings`, '{
          "ProductRating": {
            "EndOfAppealPeriod": "Somehow EndDate + 4 days and 9 hours"
          }
        }');

The main problem is that I don’t know how to reference EndDate column in set statement. How to do it?

>Solution :

UPDATE ratingperiods
SET Settings = JSON_SET(Settings, 
                        '$.ProductRating.EndOfAppealPeriod',
                        DATE_FORMAT(EndDate + INTERVAL '4 9' DAY_HOUR, '%Y-%m-%dT%H:%i:%sZ'));

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=401821393570bffbae1e593741f134f8