Append if not exists mysql json field

Question

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?

Answer 1

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