4

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?

David542
  • 104,438
  • 178
  • 489
  • 842

1 Answers1

7

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
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • thanks, one question about this is when I try doing `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. – David542 Dec 08 '18 at 00:28
  • 3
    It needs to be `'"orange"'` to create a valid JSON string. – Barmar Dec 08 '18 at 00:32