CODEWITHSUNDEEP
mysqljson
jirian
jirian

Reputation: 3

JSON_SEARCH in MySQL

I have a MySQL table with JSON data and I'm trying to get the path to a value saved in JSON. But I am not able to do that.

I just have one window:

+----------------------------------------------------------------------
| info
+----------------------------------------------------------------------
|  [{"id": "1","name":"firstname","value":"firstvalue"}, 
|  {"id":"0","name":"secondname","value":"secondvalue"}] 
+----------------------------------------------------------------------

and I'm trying to get path to value "1" of key "id" by:

SELECT JSON_SEARCH(info, "all", "1", NULL, "$*.id")

There is no problem with selecting window, but with JSON_SEARCH command (I always get NULL). What do I do wrong?

It is not How to search JSON data in MySQL?.

Upvotes: 0

Views: 3370

Answers (1)

Barmar
Barmar

Reputation: 782407

The path should be $[*].id to indicate that you want to search the id property of all array elements.

SELECT JSON_SEARCH(info, "all", "1", null, '$[*].id')

Upvotes: 3

Related Questions