Return JSON infos in MYSQL in SELECT itself

Question

Return JSON infos in MYSQL in SELECT itself

Navigation

#1 by (0 votes)

1

I have a table in the DB called "menu", with a column called STRUCTURE. It was of type text, and it contains a JSON with a menu structure referenced by IDs of another table "category".

Ex: ["id": 1}, {"id": 3}, {"id": 4}, {"id": 131} : 5]]

These IDs above are categories that are listed in the "category" table. However, to return in PHP, it has been very costly to perform, loop, within that 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 infos of each ID of this JSON, in a query only?

Example of what I would like (I do not 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

asked by anonymous 26.12.2018 / 23:49

1 answer

How to display data from a TXT in my arrayList - Android Studio [closed] The route does not run when you call after PayPal checkout

score 0 · Answer 1

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 look for categories whose id (catId) is present in the 'id' field of some of the entries in your JSON array 'mentructure'.

The result will come in columns:

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