JSON_SEARCH in MySQL

Question

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?.

It's a duplicated question. Refer to https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql — caot, Sep 19 '18 at 18:16
Possible duplicate of [How to search JSON data in MySQL?](https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql) — ninjaproger, Sep 19 '18 at 18:38

score 3 · Accepted Answer · answered Sep 19 '18 at 18:24

3

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')

answered Sep 19 '18 at 18:24

Barmar

741,623
53
500
612

Thank you a lot! Is there a way to get the "$[0].id" without ".id"? – jirian Sep 19 '18 at 18:27
You can leave out the path entirely, but then it might find `1` in some other property that you don't care about. – Barmar Sep 19 '18 at 18:31

JSON_SEARCH in MySQL

1 Answers1