mariadb - MySQL how to search JSON Array or JSON_CONTAINS in where statement with column name

Question

I am currently using MySQL 5.7. Products table contains a column to store category ids. These ids are stored in a JSON string. I am looking for the most efficient method to count the numbers of products in each categories.

I have the following categories and products tables

Categories:

id	name
1	clothes
2	Electronics

question from:https://stackoverflow.com/questions/66060102/mysql-how-to-search-json-array-or-json-contains-in-where-statement-with-column-n

Answer 1

SELECT
    `Categories`.`id`,
    `Categories`.`name`,
    (
    SELECT
        COUNT(`id`)
    FROM
        `Products`
    WHERE
        JSON_CONTAINS(
            `Products`.`category_id`,
            CONCAT('"',`Categories`.`id`,'"')
        )
) AS `products_count`
FROM
    `Categories`
ORDER BY `products_count`

fiddle

Values in JSON have string type whereas in products table they are numbers. MySQL won't convert datatypes for JSON implicitly rather than another datatypes, because " chars in JSON are not only datatype marks but, from MySQL looking point, they are a part of value. So add dquote chars to the value to be searched for.