1
I have a table in BD called "menu", with a column called STRUCTURE. It was text-like, and in it contains a JSON with a menu structure referenced by Ids from another table "category".
Ex: [{"id": 1}, {"id": 3}, {"id": 4}, {"id": 131}, {"id": 125}, {"id": 5}]
These Ids above are categories registered in the table "category".
However, to return in PHP, it has been very expensive for the performance, to make a loop, inside this loop make a SELECT * FROM categoria WHERE id = 1, = 2
, and so on...
I read that there is a new type of column called JSON, which could reference information at the time of the query. Correct me if I’m wrong.
But it is possible, for example, to make a query of this table "menu", and in it, return the instructions of each ID of this JSON, in a query only?
Example of what you would like (I don’t know the syntax)
SELECT *, JSON_EXTRACT(menEstrutura, '$.*') as Data FROM sistema_menu WHERE menId = 1
Results:
ID | Title | Structure | Data
1 | Menu Header | [{"id": 1}, {"id": 3}] | [{"1":{"id":1,"title":"Cat 01","active":"true"},"2":{"id":2,"title":"Cat 02","active":"true"}}]
Have some people using the JSON_CONTAINS function to perform Join. I have not tested but it is worth taking a look: https://stackoverflow.com/a/39818776/6432257
– Paulo M.
Thanks @Paulo M. I could not adapt to my model example, I continue in the fight. In the example template, the fields are returned in the same JOIN, but all return as null, and not in the JSON format you would like
– Maykel Esser