Query by index in column of type JSON

Asked

Viewed 85 times

1

How to perform a query, by index, where column data is of type JSON?

For example:

I want to get all 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"}
mysql
  • 1

    Since 5.7 has a lot of https://dev.mysql.com/doc/refman/5.7/en/json.html, I think you can use it JSON_CONTAINS / JSON_EXTRACT, as long as the column type is JSON. Now I would sincerely say that the best would be to create columns or even a relational structure instead of saving as JSON "pure".

1 answer

3


I believe you can use JSON_EXTRACT, but if it is JSON type can already do so:

... WHERE `extra`->'$.tensao' = 200

You can also use the JSON_CONTAINS, something like:

... WHERE JSON_CONTAINS(`extra`, 200, '$.tensao')

I never got to test JSON_CONTAINS with columns other than the type JSON.

Extra:

If it is a simple database, I see no problem in using JSON, now assuming that the database needs to make complex queries, maybe you should choose not to use JSON, but a divided relational structure (as needed) in order to make comparisons and extract data.

For example a stock has X products, all products share that value, so it would be interesting to have a table for values and another for products, would be a structure N:N (Many-to-Many).

Enjoy and read about the JOIN: What is the difference between INNER JOIN and OUTER JOIN?

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.