MySQL merge json field with new data while removing duplicates, where the json values are simple scalar values

Question

Suppose that I have a MySQL table with a JSON field that contains only numbers, like this (note: using MySQL 8):

CREATE TABLE my_table (
    id int,
    some_field json
);

Sample data:

id: 1 some_field: [1, 2, 3, 4, 5]

id: 2 some_field: [3, 6, 7]

id: 3 some_field: null

I would like to merge another array of data with the existing values of some_field, while removing duplicates. I was hoping that this might work, but it didn't:

update my_table set some_field = JSON_MERGE([1, 2, 3], some_field)

The result of this would be:

id: 1 some_field: [1, 2, 3, 4, 5]

id: 2 some_field: [1, 2, 3, 6, 7]

id: 3 some_field: [1, 2, 3]

Possible duplicate, if you want a MySQL solution: stackoverflow.com/questions/57529216/… — Bill Karwin
– Bill Karwin, Commented May 11, 2022 at 22:54
Otherwise consider fetching the array into a client application, merging whatever values you want, then UPDATE the row in the database with the modified array. — Bill Karwin
– Bill Karwin, Commented May 11, 2022 at 22:55

Trishant Pahwa · Accepted Answer · 2022-05-11 22:54:25Z

0

Considering you have 3 records in your table and you want to merge 1 and 2 as mentioned in your example.

I hope JavaScript is suitable to follow through for you.

// Get both the records

const records = db.execute(“SELECT id, some_field FROM my_table WHERE id=1 OR id=2”);

// You get both the rows.
// Merging row1, you can either use the Set data structure if you’re dealing with numbers like your example, or you could loop using a map and use the spread operator if using JSON. Since your object is an array, I’ll just be explaining to merge 2 arrays.

records[0].some_field = Array.from(new Set(records[0].some_field + record[1].some_field))

// Same for second record.

records[1].some_field = Array.from(new Set(records[0].some_field + record[1].some_field))

// Now update both the records in the database one by one.

answered May 11, 2022 at 22:54

Trishant Pahwa

3,0413 gold badges18 silver badges34 bronze badges

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

1 Comment

Darren Gates Over a year ago

Unfortunately, if I have thousands of records that require updating, this proposed solution starts to really degrade performance-wise.

Collectives™ on Stack Overflow

MySQL merge json field with new data while removing duplicates, where the json values are simple scalar values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related