MySQL JSON array in Where in condition

Question

It's working but returning just one Record. I Have 5 in Total.

Catalog.similar is json type field. I remove [""] from the return and putting inside the WHERE IN Condition. But I get just one record.

SELECT * FROM similar_codes a WHERE a.id IN (SELECT REPLACE(SUBSTR(c.similar,2,LENGTH(c.similar)-2),'"','') FROM catalog c WHERE c.id=1)

The Return from the select inside the WHERE IN is.

SELECT REPLACE(SUBSTR(c.similar,2,LENGTH(c.similar)-2),'"','') FROM catalog c WHERE c.id=1)

3380, 3382, 1952, 3384, 3383

it's always bad idea to save list in filed. You need to create similar table which will be linked to catalog. Then just use simple join. — Ilya Kovalyov
– Ilya Kovalyov, Commented May 15, 2017 at 12:34

D Lowther · Accepted Answer · 2017-05-15 13:14:28Z

2

Rather than trying to develop your own way to search JSON, why not use the built in functions? Alternatively, if you are using a version of MySQL that doesn't support JSON data, you might be better looking at using a linked table.

-- Create main table
CREATE TEMPORARY TABLE test (id INT(11));
INSERT INTO test (id) VALUES (3380), (3382), (1952), (3384), (3383);

-- Create JSON table
CREATE TEMPORARY TABLE test_search (id INT(11), similar JSON);
INSERT INTO test_search (id, similar) VALUES (1, JSON_ARRAY(3380, 3382, 1952, 3384, 3383));

-- Select
SELECT t.id 
FROM (SELECT similar AS ids FROM test_search WHERE id = 1) ts
INNER JOIN test t ON JSON_CONTAINS(ts.ids, CAST(t.id AS json), '$');

answered May 15, 2017 at 13:14

D Lowther

1,6191 gold badge10 silver badges16 bronze badges

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

2 Comments

Rafael Over a year ago

Thank's. It's Worked

Prasad Over a year ago

Precise answer!

Remco K. · Accepted Answer · 2017-05-15 12:33:03Z

0

You can use GROUP_CONCAT()

SELECT * FROM similar_codes a WHERE a.id IN 
(SELECT GROUP_CONCAT(REPLACE(SUBSTR(c.similar,2,LENGTH(c.similar)-2),'"','')) FROM catalog c WHERE c.id=1)

answered May 15, 2017 at 12:33

Remco K.

7274 silver badges23 bronze badges

3 Comments

Rafael Over a year ago

keep returning one record. the first 3380

Remco K. Over a year ago

And what does this query return? SELECT GROUP_CONCAT(REPLACE(SUBSTR(c.similar,2,LENGTH(c.similar)-2),'"','')) FROM catalog c WHERE c.id=1;

Rafael Over a year ago

Get all similars. ["3380", "3382", "1952", "3384", "3383"]

Collectives™ on Stack Overflow

MySQL JSON array in Where in condition

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related