mysql: upsert row and json field

CommonSenseCode 0
Viewed 18

I have this table:

CREATE TABLE `foo` (
  `user_id` varchar(255) NOT NULL,
  `settings` json NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `IDX_5471ee230b9746dfb775d6c354` (`user_id`)
)

I want to make a query that upserts user_id and settings (which is JSON).

I currently have this query, it upsers user_id as expected but the problem is that it overwrites the json settings.

REPLACE INTO foo(user_id, settings)   
VALUES (
    '3',JSON_SET(
            settings,
            "$.topLevelField", 
                JSON_OBJECT('nestedField1', true, 
                            'nestedField2', "01-01-2020")
        )
);
mysql-workbench mysql
1 Answers

MySQL's REPLACE is basically a DELETE followed by an INSERT. It can only replace values with the values you specify in your VALUES clause. The VALUES clause can't use the previous values stored in the row.

You might be able to use INSERT ... ON DUPLICATE KEY UPDATE.

insert into foo values ('3', JSON_SET('{}', '$.topLevelField', 
  JSON_OBJECT('nestedField1', true, 'nestedField2', '01-01-2020')))
on duplicate key update settings = JSON_SET(settings, '$.topLevelField', 
  JSON_OBJECT('nestedField1', true, 'nestedField2', '01-01-2020'));

This does take into account the previous value of settings in the on duplicate clause.

I used '{}' as a dummy value of settings in the first line just so I can make the JSON_SET() work similarly in both clauses.

Bill Karwin0
Related