CODEWITHSUNDEEP
mysql
lgc_ustc
lgc_ustc

Reputation: 1664

Preventing duplicates in JSON array in MySQL

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!

Upvotes: 2

Views: 4301

Answers (4)

Muthu Kumar
Muthu Kumar

Reputation: 1

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

Upvotes: 0

Mio
Mio

Reputation: 21

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]

Upvotes: 0

Luv
Luv

Reputation: 199

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] |

Upvotes: 1

Nick
Nick

Reputation: 147146

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"]

Upvotes: 3

Related Questions