Is there a JSON function in mysql that will ignore trying to add the element if it already exists? For example:
update waitinglist SET
new = JSON_ARRAY_APPEND(new, '$', "orange")
where id=2;
update waitinglist SET
new = JSON_ARRAY_APPEND(new, '$', "orange")
where id=2;
Now my array looks like:
["apple", "orange", "orange", "orange", "orange"]
But I want it to work like a set, and just be:
["apple", "orange"]
Is there a way to do this?
I don't think so. You can test whether the value is already in the JSON in the WHERE clause.
update waitinglist SET
new = JSON_ARRAY_APPEND(new, '$', '"orange"'))
where id=2
AND NOT JSON_CONTAINS(new, '"orange"')
If you're updating multiple columns and need this to affect just this one column, you can use IF() to leave it unchanged if the value is already there.
update waitinglist SET
new = IF(JSON_CONTAINS(new, '"orange"'), new, JSON_ARRAY_APPEND(new, '$', '"orange"'))
where id=2
JSON_CONTAINS(new, 'orange') I get the following sql error: Invalid JSON text in argument 2 to function json_contains: "Invalid value.". Is this only available on mysql8? I'm using 5.7.
'"orange"' to create a valid JSON string.I prefer MEMBER OF:
IF("orange" MEMBER OF new, new, JSON_SET(new, '$[999]', "orange"))
because it works with plain strings instead of the quotes-inside-quotes construct.
Where this matters is when you want to use fields rather than literals:
IF(x.Fruit MEMBER OF new, new, JSON_SET(new, '$[999]', x.Fruit))
I've had zero luck getting JSON_CONTAINS to accept values from a field. Yes I've tried CONCAT to get various configurations of quotes. Works great with a literal though. I'm using the AWS Aurora MySQL clone not MySQL per se so that might be an issue.
Yeah, yeah, the '$[999]' feels hacky--you need to specify an unused index to make JSON_SET append values to an array--but JSON_ARRAY_APPEND and JSON_ARRAY_INSERT require gymnastics to consume things that aren't literals.
Bonus gotcha: This doesn't work with null JSON fields but you can't set a default for JSON fields. So you have to do SET x.Fruit='[]' first. Sure, you can put the '[]' in as part of your INSERT, but when you're retrofitting a JSON column to an existing table it's an annoyance that could've been avoided. Again, this is AWS Aurora MySQL, maybe real MySQL works better.
