MYSQL Search data from json field where datas position might differ

Question

I have table where i have to find ID when i only know for example dcid:xxxxxx which can be found in different places in IDENTIFIERS column

+----ID----+--------------------------------IDENTIFIERS-----------------------------------+
|  234512  | ["ident:2392392", "id2:292930", "dcid:291013"]                               |
|  564344  | ["ident:9402933", "dcid:29101", "id3:2092819"]                               |
|  655656  | {"1":"ident:0291911", "2":"id2:9522211", "3":"id3:8877543", "4":"dcid:92366"}|
+----------+------------------------------------------------------------------------------+

Answer 1

You can do this:

SELECT ID FROM mytable
WHERE JSON_SEARCH(identifiers, 'dcid:291013');

The JSON_SEARCH() function returns the JSON path to the value if it can find it, regardless of the position or type of JSON document. If the value cannot be found anywhere, then JSON_SEARCH() returns NULL.

This query cannot be optimized with an index. It will be bound to do a full table-scan, so it will get more and more slow as your table grows.

My standard recommendation is if you need to use JSON functions to search data in the WHERE clause, then it's a strong indicator that you should store data in normal rows and columns, not in JSON.