How do I change an array-valued key of a json field in a MYSQL database using JSON_REPLACE and JSON

I'm trying to change one of the array-valued keys (called religions) of a JSON field named preferences in a MySQL table.

I'm not using JSON_ARRAY_APPEND because the changes in the field can be arbitrary and can involve both removals and additions.

Right now I'm trying to change it by using the following query.


const religionPreferences = ["Buddhism","Christianity","Non religious"]
const sql = `UPDATE users SET preferences = JSON_REPLACE(
    preferences,
    '$.${key}',
    JSON_ARRAY(religionPreferences[0], religionPreferences[1], religionPreferences[2])
)
WHERE id = "${req.params.id}" LIMIT 1`

The problem is that the religionPreferences array can have anywhere from 0-14 elements and I wasn't sure how to proceed with variable arguments having to be passed into the JSON_ARRAY function.

const religionPreferences = ["Buddhism","Christianity","Non religious"]
const sql = `UPDATE users SET preferences = JSON_REPLACE(
    preferences,
    '$.religions',
    JSON_ARRAY(religionPreferences[0], religionPreferences[1], religionPreferences[2])
)
WHERE id = "${req.params.id}" LIMIT 1`

Another approach I tried was just directly setting the argument for JSON_REPLACE as an array literal instead of passing in JSON_ARRAY as below.


const religionPreferences = ["Buddhism","Christianity","Non religious"]
const sql = `UPDATE users SET preferences = JSON_REPLACE(
    preferences,
    '$.religions',
    ${religionPreferences}
)
WHERE id = "${req.params.id}" LIMIT 1`

The problem is that I want it to be saved as

religions: ["Christian","Buddhist","Other"]

instead of

religions: '["Christian","Buddhist","Other"]'

But when I tried to save it as an array directly without quotes around it, it said the SQL had an error..

Thanks so much. -Jay

CodePudding user response：

Here's a demo. First we set up some JSON documents for testing.

mysql> set @j = '{"religions": []}';
Query OK, 0 rows affected (0.00 sec)

mysql> set @r = '["Buddhism","Christianity","Non religious"]';
Query OK, 0 rows affected (0.00 sec)

What you don't want is to treat @r as a string, and set it as the value for the "religions" key. This is the default, because otherwise there would be no way to set a value to a string that happens to contain characters that appear like a JSON document.

mysql> select json_replace(@j, '$.religions', @r);
 -------------------------------------------------------------------- 
| json_replace(@j, '$.religions', @r)                                |
 -------------------------------------------------------------------- 
| {"religions": "[\"Buddhism\",\"Christianity\",\"Non religious\"]"} |
 -------------------------------------------------------------------- 
1 row in set (0.00 sec)

But if you explicitly cast the value as a JSON document, then you can do what you want. It sets the value for the "religions" key to be a JSON array.

mysql> select json_replace(@j, '$.religions', cast(@r as json));
 -------------------------------------------------------------- 
| json_replace(@j, '$.religions', cast(@r as json))            |
 -------------------------------------------------------------- 
| {"religions": ["Buddhism", "Christianity", "Non religious"]} |
 --------------------------------------------------------------