How to use Column with JSON string with IN Operator in SQL?

Question

I have one column with JSON data, i want to use that column with my query, but i was try so many thing but can't please guide me for that How to use Column with JSON string with IN Operator in SQL ? i have following table and query.

table1 
------------
id | ids    |
1  | [1,2]  |
2  | [3,4]  |
3  | [5]    |
4  | []     |
5  | [1,5,6]|
-------------

table2
------------
id | name   |
1  | raj    |
2  | mohan  |
3  | test   |
4  | name1  |
5  | hello  |
-------------



SELECT * FROM `table1` t1 
LEFT JOIN table2 t2 ON ( t2.id IN (replace(replace(t1.ids,"[",""),"]","")) )
WHERE p.id = 2

It seems like an odd requirement, and I feel like the better option would be to restructure how those values are stored, so you can use them database the way it is intended. However, if you have no choice, I think you're on the right track. I would try simplifying your query to test the principle, so just do a simple `select * from table1`, with your where clause above, which might make it easier to try things. My guess is that the problem is to do with data types - perhaps the id column in table2 is a numeric type and the JSON is a string, in which case you'll need to look into casting. — DaveyDaveDave, Mar 17 '18 at 08:42

score 0 · Answer 1 · answered Mar 17 '18 at 11:00

0

You could use JSON_CONTAINS:

SELECT * 
FROM `table1` t1 
LEFT JOIN `table2` t2
  ON JSON_CONTAINS(t1.ids, t2.id)
WHERE t1.id = 2;

DBFiddle Demo

answered Mar 17 '18 at 11:00

Lukasz Szozda

162,964
23
234
275

dear it's give me error like this : **#1305 - FUNCTION utesttxhzd_db3.JSON_CONTAINS does not exist ** .. Thanks – Bhavik Hirani Mar 17 '18 at 11:54
@BhavikHirani So it looks like you're using outdated version of MySQL. – Lukasz Szozda Mar 17 '18 at 15:07
I have run my site for 3-4 years. But due to a need now I have to run this custom query. And this is not my main query, but I'm going to use this method in my main query. – Bhavik Hirani Mar 18 '18 at 05:22

Alpesh Jikadra · Answer 2 · 2018-03-17T16:04:44.970

Your IN() query might not work correctly because mysql return '1,2' for id 1, and you need something like 1,2, Check following example

select 
replace(replace(t1.ids,'[',''),']','') as id 
from table11 t1 where t1.id=1;

Above query return result as string which interpret like '1,2' not 1,2

And because of this your query might not return your expected output.

You can try some other approach, as follow

SOLUTION

Create 1 function which will convert your json string to another table and then you can use that table into your in() query

Try following function (Reference)

DROP FUNCTION IF EXISTS hello;
DELIMITER //
CREATE FUNCTION hello (_list CHAR(20))
RETURNS INT
BEGIN
    DECLARE _next TEXT DEFAULT NULL;
    DECLARE _nextlen INT DEFAULT NULL;
    DECLARE _value TEXT DEFAULT NULL;

iterator:
LOOP
  IF LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN
    LEAVE iterator;
  END IF;

  SET _next = SUBSTRING_INDEX(_list,',',1);
  SET _nextlen = LENGTH(_next);
  SET _value = TRIM(_next);

  INSERT INTO t11 (id) VALUES (_next);

  SET _list = INSERT(_list,1,_nextlen + 1,'');

END LOOP;
    RETURN 1;
END //
DELIMITER  ;

Create one table

CREATE TABLE t11(id CHAR(1));

Then execute following queries.

DELETE FROM t11;
SELECT hello(REPLACE(REPLACE(t1.ids,'[',''),']','')) FROM table11 t1;
SELECT t2.* FROM table22 t2 WHERE t2.id IN(SELECT id FROM t11);

I hope this will solve your problem

This is not my main query, but I'm going to use this method in my main query. so i can not use MySql Procedure.. Thanks . :) — Bhavik Hirani, Mar 18 '18 at 05:23

How to use Column with JSON string with IN Operator in SQL?

2 Answers2