How in MySQL to create a select query nested json array?

0 like 0 dislike
16 views
Hello!
There is json, which is written in the data_json with the type "json" in a MySQL table:
{ "name": "Test", "books": [ { "id": 1 }, { "id": 2 } ] }

How do I make a selection id array books where id = 1?

I tried something like that, but it doesn't work:
SELECT * FROM test WHERE data_json -> "$.books[*].id" = 1
by | 16 views

1 Answer

0 like 0 dislike
No, or I don't know how.

The fact that the sample data_json -> "$.books[*].id" return here is the value "[1, 2]" it is incorrect to compare the number.

SELECT data_json -> "$.books[*].id" FROM test

In MySQL 8 have JSON_TABLE, guessing from "[1, 2]" you can make a table and have it build the condition.
by

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
7 answers
asked Mar 28, 2019 by ChemAli
0 like 0 dislike
1 answer
asked May 20, 2019 by Dvoeglazov
0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
asked Jun 10, 2019 by jcmax
110,608 questions
257,186 answers
0 comments
28,759 users