A use case for MySql JSON datatype

Question

I am creating a DB-schema for a website on which users can write the Articles. I was almost done with the design and suddenly I read few blogs on JSON datatype in MySQL.
As per blogs, there are certain use cases where JSON can be used:

1. for storing metadata. e.g a product having its height, widths, colour stored as JSON.
2. for storing the non-standard schema type data
3. for storing the tags as JSON. e.g this question could have tags - mysql, JSON. So the blogs recommended using a JSON structure that holds all the tags.

The last one is doubtful to me. Why?
Ok I have stored the tag value in JSON as {"tags": ["mysql", "JSON", "mysql-datatype"]}. I agree this helps in easily maintaining the tags with the Article.
But suppose a user wants to read all the article related to mysql tags!! If I have been maintained a separate table for article_id - tags_id, I could have easily get all the Articles based on the tags. But with JSON this could be a very hectic requirement, though this can be solved but with a cost. Slower queries ofcourse.
This is my schema for Article: Is my way of thinking correct or am I missing something here? Love to hear some suggestions.

Answer 1

The task you're trying to do, to associate articles with tags, is better handled as a many-to-many relationship. For this you need another table, which I believe is the article_tags table in your diagram.

This makes it easy to query for all articles with a given tag.

SELECT ...
FROM article AS a
JOIN article_tags AS t USING (article_id)
WHERE t.topic_id = 1234 -- whatever is the id for the topic you want to read

Doing the same thing if you use JSON to store tags in the article table is different:

SELECT ...
FROM article AS a
WHERE JSON_CONTAINS(a.article_tags, '1234')

This might seem simpler, since it does not require a JOIN.

But any search that puts the column you need to search inside a function call will not be able to use an index. This will result in a table-scan, so the query will always search every row in the table. As your table grows, it will become slower and slower to do this search "the hard way."

The first method with the article_tags table uses an index two ways:

1. Look up the entries in article_tags matching the desired tag quickly
2. Look up the corresponding articles by their primary key quickly

No table-scan needed. The query reads only the rows that are going to be in the query result.

My take on the JSON data type and JSON functions follows this general rule:

Reference JSON columns in the select-list, but not in the WHERE clause.

That is, if you can do your search conditions in the WHERE clause using non-JSON columns, you can take advantage of indexes to make the query as efficient as possible.

Once the relevant rows have been found that way, then you may extract parts of your JSON data to return in the result. Compared to the cost of searching JSON documents in a table-scan, the cost of extracting a field from the JSON documents on the rows matching your search is relatively small.

The select-list is not evaluated for rows unless they match the search conditions.

Answer 2

I haven't used it myself yet, but from my understanding I wouldn't use JSON for items you'd want to lookup / filter by. For example: I'd use it for storing a JSON config where the config schema might change frequently (meaning no DB schema changes).

However it looks like MySQL does have functions to search in the JSON, https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

JSON_CONTAINS(target, candidate[, path])

Not sure on the efficiency of this compared to an indexed string column.