How do I perform a query, by index, where column data is of type JSON?
For example:
I want to get all the data where the voltage is equal to 220:
table material
id | descricao | extra
2 | Altus | {"tensao": "220", "comunicacao": "2"}
5 | Sonesse 406 | {"tensao": "110", "comunicacao": "2"}
7 | Sonesse 409 | {"tensao": "220", "comunicacao": "2"}
I think you can use JSON_EXTRACT , but if for the JSON type can already do this:
... WHERE 'extra'->'$.tensao' = 200
You can also use JSON_CONTAINS , something like:
... WHERE JSON_CONTAINS('extra', 200, '$.tensao')
I did not even test JSON_CONTAINS with columns other than JSON .
If it is a simple database, I do not see any problem in using JSON, now assuming that the database needs complex queries, maybe you should choose not to use JSON, but rather a relational structure divided (as needed) to be able make comparisons and data extractions.
For example a stock has X products, all products share that value, so it would be interesting to have one table for values and another for products, it would be a N:N (many-to-many) structure.
Enjoy and read about JOIN : What's the difference between INNER JOIN and OUTER JOIN?