Preventing duplicates in JSON array in MySQL

Question

I have a JSON-typed column in a MySQl table. This column stores an array of integer values, like '[1,2,3]'.

What I want is: when I insert a new value into this array using JSON_ARRAY_INSERT/JSON_ARRAY_APPEND, if the value already exists, simply ignore it.

How can I do this within MySQL? That is, NOT checking duplicates from calling scripts like PHP or Node.js, etc.

Update: I'd prefer to use a built-in MySQL mechanism for this purpose, because if done in external scripts, there would be a small chance that duplicates happen due to race conditions, even if I check against duplicates before storing the data.

Thanks!

I do store normalized data. However, due to race conditions, there is a small chance that dups might happen if this is done outside MySQL. — lgc_ustc
– lgc_ustc, Commented Apr 25, 2018 at 8:10

Nick · Accepted Answer · 2018-04-25 02:52:03Z

3

Without knowing your exact table structure it's difficult to give a precise answer, but the following queries demonstrate how it's done with variables. You would just need to adapt this according to your columns.

set @j='["1","2","3"]';
set @v = "4";
select ifnull(json_search(@j, 'one', @v), @j:=json_array_append(@j, '$', @v));
set @v = "1";
select ifnull(json_search(@j, 'one', @v), @j:=json_array_append(@j, '$', @v));
select @j;

Output:

["1", "2", "3", "4"]

answered Apr 25, 2018 at 2:52

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CaptRisky Over a year ago

search only works on strings not int's so if you store an an array of int's, this solution won't work, and it's likely int's will be more common like the op question.

Luv · Accepted Answer · 2020-06-05 17:33:12Z

1

Hope this helps.

Schema (MySQL v5.7)

CREATE TABLE test (
  id INT,
  js JSON
);
INSERT INTO test (id, js) VALUES (1, '[1,2,3]');
INSERT INTO test (id, js) VALUES (2, '[1,2]');

Query #1

select * from test;

| id  | js        |
| --- | --------- |
| 1   | [1, 2, 3] |
| 2   | [1, 2]    |

Query #2

UPDATE test
SET js = JSON_ARRAY_APPEND (js, '$', 3)
WHERE id = 1 and not JSON_CONTAINS(js, '3', '$');

UPDATE test
SET js = JSON_ARRAY_APPEND (js, '$', 3)
WHERE id = 2 and not JSON_CONTAINS(js, '3', '$');

Query #3

select * from test;

| id  | js        |
| --- | --------- |
| 1   | [1, 2, 3] |
| 2   | [1, 2, 3] |

answered Jun 5, 2020 at 17:33

Luv

3094 silver badges16 bronze badges

Comments

Mio · Accepted Answer · 2022-05-24 09:36:22Z

0

set @j='[1, 2, 3]';
set @v=1;
SELECT IF(JSON_CONTAINS(@j, CAST(@v AS JSON), '$') = 1, @j, json_array_append(@j, '$', CAST(@v AS JSON)));

OUTPUT: [1, 2, 3]

set @j='[1, 2, 3]';
set @v=4;
SELECT IF(JSON_CONTAINS(@j, CAST(@v AS JSON), '$') = 1, @j, json_array_append(@j, '$', CAST(@v AS JSON)));

OUTPUT: [1, 2, 3, 4]

answered May 24, 2022 at 9:36

Mio

213 bronze badges

Comments

marc_s · Accepted Answer · 2023-02-28 21:21:38Z

0

DECLARE lc,leng,du INT(50);

DECLARE conc,vb VARCHAR(50);
DECLARE new_array JSON;
SET new_array='[]';

SET lc=0;
SET leng=JSON_LENGTH(first_array);
WHILE lc<leng DO
SET conc=(SELECT CONCAT('$[',lc,']'));

IF JSON_CONTAINS(JSON_UNQUOTE(new_array),CONCAT('"',JSON_UNQUOTE(JSON_EXTRACT(first_array,conc)),'"'))  THEN
  
   SET du=1;
ELSE
    SET new_array=(SELECT JSON_ARRAY_APPEND(new_array,'$',(JSON_UNQUOTE(JSON_EXTRACT(first_array,conc)))));
end if;
SET lc=lc+1;
END WHILE;


RETURN new_array;

This function returns non duplicate JSON array in MySQL first array is a json_array input parameter

edited Feb 28, 2023 at 21:21

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 1, 2022 at 9:42

Muthu Kumar

11 bronze badge

Collectives™ on Stack Overflow

Preventing duplicates in JSON array in MySQL

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related