0

I have a database with a column that has a set of IDs in it (db.cache):

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

And then in my query I'm trying to join from an existing table over to this caching table and then only get the rows where the id from table a is not in this list:

SELECT id 
FROM a 
LEFT JOIN b on b 
WHERE JSON_SEARCH(b.`cache`, 'one', a.id) IS NULL

However when running this I'm getting the following:

Error Code: 3141. Invalid JSON text in argument 1 to function json_search: "Invalid value." at position 3.

If, however, I put that clause in the select, I get a listing of rows with either the JSON path or a NULL, and if I change from IS NULL to IS NOT NULL in the where clause it also works as expected.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
J_D
  • 681
  • 2
  • 8
  • 22
  • `Left Join b On b`.. this does not look right.. remove `on b` and try again – Madhur Bhaiya Aug 24 '19 at 03:30
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Progman Aug 24 '19 at 08:48
  • Please [edit] your question to include a [mcve], which can be tested by others. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Progman Aug 24 '19 at 08:49
  • @madhur assume in this case table b has one row only. Please note in the question this works as a "is not null" it works – J_D Aug 24 '19 at 12:10
  • @progman this is not a duplicate as I'm not asking if it's a good idea, I'm trying to find a solution as to why mysql returns an error in a IS NULL case but returns fine in the same statement as IS NOT NULL. – J_D Aug 24 '19 at 12:12
  • I can't reproduce the problem, see [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d1d24c44c3ab11169379cab5b660afd2). – wchiquito Aug 25 '19 at 10:41

1 Answers1

1

You might want to check that your JSON is valid before searching on it:

SELECT id 
FROM a 
LEFT JOIN b on b 
WHERE 
JSON_VALID(b.`cache`)=1  -- check that b.`cache` is valid JSON
AND JSON_SEARCH(b.`cache`, 'one', a.id) IS NULL
w. Patrick Gale
  • 1,643
  • 13
  • 22