1

There's a hard to understand issue with querying on a json field in MySQL. The data column is of type json.

The following query works perfectly fine

SELECT * FROM `someTable` WHERE data->'$.someData' in ('A')

However the following one returns nothing.

 SELECT * FROM `someTable` WHERE data->'$.someData' in ('A','B')

Funnily enough this also works:

 SELECT * FROM `someTable` WHERE data->'$.someData'='A' OR data->'$.someData'='B'

I'm clueless as to why this happens. I originally thought that WHERE x IN executed in a json query format might be doing something like && but even if the values are ('A','A') it still returns nothing which essentially shows that more than one value in WHERE x IN wont work.

SAMPLE DATA (any would do really)

id | data (json)
1  | {"someData":"A"}
2  | {"someData":"B"}
Nick
  • 138,499
  • 22
  • 57
  • 95
Return-1
  • 2,329
  • 3
  • 21
  • 56
  • Do you have any sample data demonstrating the problem? – Nick Apr 12 '19 at 08:13
  • any data would do. assume a table with only two columns, id and data where data is of type json. i added some on the question description – Return-1 Apr 12 '19 at 08:14

2 Answers2

5

Too long for a comment...

This seems to be related to an optimisation MySQL is performing when there is only one value in the IN expression (probably converting it to an a = b expression) and then it ignoring quotes. Strictly speaking, 

SELECT * 
FROM `someTable` 
WHERE data->'$.someData' in ('A')

or

SELECT * 
FROM `someTable` 
WHERE data->'$.someData' = 'A'

should return no data because 

SELECT data->'$.someData' 
FROM someTable;

returns 

"A"
"B"

which is not the same as A. You need to use JSON_UNQUOTE (or if you have MySQL 5.7.13 or later the ->> operator) to get the actual value of the someData key:

SELECT JSON_UNQUOTE(data->'$.someData') FROm someTable;
SELECT data->>'$.someData' FROm someTable;

which gives

A
B

which then works fine with an IN expression:

SELECT * 
FROM `someTable` 
WHERE JSON_UNQUOTE(data->'$.someData') in ('A','B')
-- or use WHERE data->>'$.someData' in ('A','B')

Output:

id  data
1   {"someData":"A"}
2   {"someData":"B"}

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • MySQL also provides the `->>` operator which does the same thing -- unquotes a scalar value extracted by the JSON path. – Bill Karwin Apr 12 '19 at 15:01
  • @BillKarwin thanks for the reminder. I've added notes about that to the answer. – Nick Apr 13 '19 at 00:34
0

You could try using a join on a subquery instead of a IN clause 

SELECT * 
FROM `someTable` s 
INNER JOIN (
   select 'A'  col
   union 
   select 'B'
 )  t ON t.col = s.data->'$.someData
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107