0

I have a table with two columns : id(int), data(text)

data holds an array of strings. I want to insert strings into it, if they're not already present.

Searched a bit, came up with this :

INSERT INTO schema_name.table_name(id, data) VALUES(1, '["3"]') 
ON DUPLICATE KEY UPDATE data= IF(JSON_CONTAINS(data,'3', '$'), JSON_ARRAY_APPEND(data, '$', '3'), data)

It does not insert "3" into data, whether or not it's already present. It doesn't return an error. I'm wondering how should I do it, and why this does not work

Thank you

4
  • It might help if you created a reproducible fiddle. Also, is this question really about both SQL Server and MySQL?
    – mustaccio
    Dec 22, 2022 at 13:25
  • ON DUPLICATE fires ONLY as a reaction on some UNIQUE constraint violation. What unique index in your table allows to detect that the value '["3"]' is a duplicate?
    – Akina
    Dec 22, 2022 at 16:56
  • Ugh. JSON is grossly inefficient to manipulate. Can't you lay out the schema some other way? Explain what kind of data you are storring there.
    – Rick James
    Dec 22, 2022 at 21:05
  • @RickJames I have two tables. One to store (message_id, message_text), the other one to store the message_ids for sent by a specific user(user_id, message_id_list). This is to make searching all messages of a user more efficient.
    – lyeaf
    Dec 22, 2022 at 21:24

3 Answers 3

1

I think you forget to add !, check the below example it adds any new number except [1-4].

SET @list = '["1","2","3","4"]';
# try existing number(1,2,3, and 4), they won't add to the list
SET @item_list = '["5"]'; 
SET @item = '5';


select IF(!JSON_CONTAINS(@list,@item_list, '$'), JSON_ARRAY_APPEND(@list, '$', @item), @list);
1
  • You're right. I'll switch the IF statement 2nd and 3rd argument which will be the same as adding a ! Thanks :) I also had a second issue which I explained in my 2nd post
    – lyeaf
    Dec 22, 2022 at 21:32
1

the other one to store the message_ids for sent by a specific user(user_id, message_id_list).

That is a simply one-to-many relationship between Users and Messages. It is easily and efficiently implemented by having user_id in the messages table and have INDEX(user_id) on that table. No JSON (for this purpose); not slow.

3
  • The thing is user_id here will be a non-unique index. If I do SELECT * FROM messages WHERE user_id=1, it will take 40~ seconds to perform. Where if SELECT the different message_ids it will be instant. But I can't know what message_ids I need to fetch unless I have another table listing a user's message_ids Unless I'm misunderstanding something here.
    – lyeaf
    Dec 22, 2022 at 23:47
  • 40 Seconds?? How many rows in the table? How many rows in the result set? Do you have the INDEX I suggested? Please provide the SELECT and EXPLAIN SELECT ...
    – Rick James
    Dec 23, 2022 at 3:40
  • 1
    Oh I get it now. I first thought that all columns had an index, and that for some reason if they were not UNIQUE index then it would just take a lot of time(I know very little about SQL databases). I see now how to add an INDEX. It's created. And the searching is instant, thank you very much ! I feel like I discovered a new superpower lol
    – lyeaf
    Dec 23, 2022 at 7:54
0

Fixed it. There were two issues :

  1. For some reasons, if I want to check if string "3" is present in the array, I need to give JSON_CONTAINS '"3"' as second argument, and not '3' However, for JSON_ARRAY_APPEND '3' is enough.

  2. I had my IF statement's 2nd and 3rd argument switched up. As @Niyaz noted it.

This query worked :

INSERT INTO schema_name.table_name(id, data) VALUES(1, '["3"]')

ON DUPLICATE KEY UPDATE data = IF(JSON_CONTAINS(data, '"3"', '$'), data, JSON_ARRAY_APPEND(data, '$', '3'))

Will set this as AA when the 2 days wait period is over

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.