1

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.

Murilo
  • 580
  • 5
  • 21

1 Answers1

1

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.

David Stokes
  • 120
  • 3
  • What do you mean by "generated column"? A normal SQL column? I have those and I use them, I omitted 90% of the query and table to focus on the problem I currently have. Sometimes user will try to filter by two custom columns and currently I am filtering on the application layer but if I want to use OFFSET and LIMIT to paginate on DB I cant do that. – Murilo Jul 26 '17 at 14:25
  • 1
    Generated or virtual columns have many uses but one of then is for taking parts of you JSON data and placing that part in its own column. That new column can be indexes for fast searches. Refer to http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/ for examples – David Stokes Jul 28 '17 at 14:36