Mysql 5.7.27: Searching JSON array

Question

I'm trying to figure out how to search a table's JSON array column. I found this Stackoverflow question but I'm unclear on how JSON_SEARCH works.

In the answers, people seem to be giving JSON_SEARCH direct data to search through:

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "2")

But since I'm making a search I don't actually have the array ["1","2","3","4","5"]

I'm creating a search query like so:

SELECT * FROM `server_list` 
WHERE
    `server_version` LIKE '%%'
AND
    `server_name` LIKE '%%'
AND 
    `server_slots` > 1500
AND
    `server_slots` < 4000

How can I also search for specific tags within that same query? Something along:

AND
    JSON_SEARCH(`server_tags`, ['1', '2', '3'])

Where server_tags is the column I want to search and ['1', '2', '3'] are multiple tags I want to search for.

Edit: I just realised JSON columns are just strings, which allows me to do this:

SELECT * FROM `lista_server` 
    `server_tags` LIKE '%value%'
    OR `server_tags` LIKE '%value2%'

Is there anything wrong with this method?

score 1 · Accepted Answer · answered Apr 18 '20 at 13:20

If you want to check for the existence of any of the tags, you can OR together the results of JSON_SEARCH for each of them:

AND (JSON_SEARCH(server_tags, 'one', '1') IS NOT NULL OR
     JSON_SEARCH(server_tags, 'one', '2') IS NOT NULL OR
     JSON_SEARCH(server_tags, 'one', '3') IS NOT NULL
    )

To check they all exist, just AND the searches together:

AND (JSON_SEARCH(server_tags, 'one', '1') IS NOT NULL AND
     JSON_SEARCH(server_tags, 'one', '2') IS NOT NULL AND
     JSON_SEARCH(server_tags, 'one', '3') IS NOT NULL
    )

Note attempting to search as text will likely not work, it's possible the values in the JSON array will not be in the same order as in your test string (e.g. ['1', '2'] won't match ['2', '1']), or the length of the JSON array could be different (e.g. ['1', '2'] won't match ['1', '2', '3']) etc.

That's fantastic, thank you for your help. Couldn't figure out how to use JSON_SEARCH at all! — lpetrucci, Apr 19 '20 at 11:09

score 1 · Answer 2 · answered Apr 18 '20 at 18:00

The problem with this method:

SELECT * FROM `lista_server` 
WHERE `server_tags` LIKE '%value%'
OR `server_tags` LIKE '%value2%'

Is that the wildcard-matching doesn't know anything about the value boundaries.

Example:

INSERT INTO lista_server SET server_tags = '112,456,789';

Then search for tag '1' using your method:

SELECT * FROM lista_server WHERE server_tags LIKE '%1%'

It matches! Because 112 is matched by '%1%'.

This is one of the many reasons it's trouble to store a comma-separated list of values when you want to search for individual values in the list.

Using JSON is just a troublesome as using a comma-separated list.

Instead, if you want to search for individual values, you need to normalize your table. Storing tags one per row in a child table:

INSERT INTO lista_server_tags (lista_server_id, tag) 
VALUES (1234, '112'), (1234, '456'), (1234, '789');

Then you can search for them as individual values:

SELECT l.* FROM lista_server AS l 
JOIN lista_server_tags AS t ON (l.id = t.lista_server_id)
WHERE t.tag = '112'

And you are sure it won't match incorrectly. It will only match the one value, not values that coincidentally contain the string you search for. You can also use an index to help speed up the search. You can add an unlimited number of tags. And many other benefits.

Could you elaborate on "You can also use an index to help speed up the search."? Fairly new to building my own Databases so not entirely sure how to do this. Do you have any resources to learn how to index? — lpetrucci, Apr 19 '20 at 11:04
I do: [How to Design Indexes, Really](https://www.slideshare.net/billkarwin/how-to-design-indexes-really) and [How to Use JSON in MySQL Wrong](https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong). — Bill Karwin, Apr 19 '20 at 14:35
You might also like my book: [SQL Antipatterns: How to Avoid the Pitfalls of Database Programming](https://pragprog.com/book/bksqla/sql-antipatterns). — Bill Karwin, Apr 19 '20 at 14:37
If you are new to using databases, I recommend you avoid using the JSON data type until you understand how to use normalized databases. — Bill Karwin, Apr 19 '20 at 14:37

score 0 · Answer 3 · answered Apr 18 '20 at 13:18

0

Absolutely nothing is wrong with what you did, just make sure your search term is clean to prevent (SQL Injection).

answered Apr 18 '20 at 13:18

Moayad .AlMoghrabi

1,249
1
11
18

Mysql 5.7.27: Searching JSON array

3 Answers3