CODEWITHSUNDEEP
mysqlmariadbmysql-5.7
Bruce
Bruce

Reputation: 1681

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

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

Products:

id name category_id
1 test 01 ["1"]
2 test 02 ["1","2"]
3 test 03 ["2"]
4 test 04 NULL

My Query:

SELECT
    `categories`.`id`,
    `categories`.`name`
    (
    SELECT
        COUNT(`id`)
    FROM
        `products`
    WHERE
        JSON_CONTAINS(
            `products`.`category_id`,
            '\"`categories`.`id`\"'
        )
) AS `products_count`
FROM
    `categories`
ORDER BY `products_count`

But i am getting products_count as 0. But if i use the value instead on column name like

JSON_CONTAINS( `products`.`category_id`, '"2"')

I am getting the correct products_count. And also with other test queries while using values.

I have tried many answers but none produce the expected results. Most of them are based on the values or inner json key/values only.

I have tested many queries but none of them actual results. Some of them getting json parameter or similar errors. Some of the tested queries are

JSON_SEARCH(`courses`.`category_id`, 'all', `categories`.`id`) 
JSON_CONTAINS( `courses`.`category_id`, `categories`.`id`, '$')
JSON_CONTAINS(`courses`.`sub_category_id`, JSON_QUOTE(`categories`.`id`), '$')

I am using MySQL 5.7, PHP 7.4

Thanks in advance...

Upvotes: 0

Views: 6996

Answers (1)

Akina
Akina

Reputation: 42834

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.

Upvotes: 4

Related Questions