Return JSON Infos in MYSQL in SELECT itself

Asked

Viewed 361 times

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"}}]
mysql json

  • 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

  • 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

1 answer

0

You can do:

SELECT 
    catId, 
    catEstrutura 
FROM categoria 
having (SELECT 1 FROM sistema_menu where menId=1 and json_contains(menEstrutura, concat('{"id" : ', catId, '}')))>0

That is, you search for categories whose id (catid) is present in the 'id' field of any of the entries in your JSON array'.

The result will come in columns:

catId | catEstrutura 
1     | {"id":1,"title":"Cat 01","active":"true"}
2     | {"id":2,"title":"Cat 02","active":"true"}

Browser other questions tagged mysql json

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