CODEWITHSUNDEEP
mysqlsqljsonmysql-json
Murilo
Murilo

Reputation: 649

Filter rows by JSON column that stores array of objects

I have the following table which its called products I omitted columns that I won't need:

+---------------------+----------------+------+-----+---------+----------------+
| Field               | Type           | Null | Key | Default | Extra          |
+---------------------+----------------+------+-----+---------+----------------+
| id                  | int(11)        | NO   | PRI | NULL    | auto_increment |
| name                | varchar(255)   | NO   |     | NULL    |                |
| custom_fields       | json           | YES  |     | NULL    |                |
| parent_variation_id | int(11)        | YES  | MUL | NULL    |                |
| variation_fields    | json           | YES  |     | NULL    |                |
+---------------------+----------------+------+-----+---------+----------------+

I have two JSON columns which I want to use in the same query to filter products and its variations. Both columns have the same structure which is something like this to store custom fields a product might have:

[
    {"name": "External use", "type": "checkbox", "value":"true"},
    {"name": "Handmade", "type": "checkbox", "value":"true"},
    ....
]

The important attributes for the query to filter are name and value which is the name of the field and the value associated to that specific product respectively, in the example above we have a product which is handmade that can be used externally.

If the user wants to filter products he might send params like {"External use": "false", "Handmade":"true"} but right now I can only filter on one attribute, currently value so if I pass true it will bring all products that have value set to true in any field.

SELECT * 
FROM   `products` 
WHERE   json_search(custom_fields, 'all', 'true', NULL, '$[*].value') IS NOT NULL

I would like to know if its possible to apply an AND condition or something alike for the same JSON object inside these array of objects, so the param I pass is related to the field I need.

Upvotes: 1

Views: 2256

Answers (1)

David Stokes
David Stokes

Reputation: 120

Yes you could. But to get performance out of such data I suggest creating a generated column (or two) and indexing it for faster queries.

Upvotes: 1

Related Questions