Path to json object which contains an attribute or value

user_5801049 0
Viewed 50

I have the following json array...

[
{ "class":"Identity", "id":5, "type":6 },
{ "class":"Combat", "damage":10.0 },
.
.
.
]

or

[
{ "class":"Combat", "damage":10.0 },,
{ "class":"Identity", "id":5, "type":6 }
.
.
.
]

I dont know the exact path to it, because its not deterministic.
I tried this select json_search('one', "Identity") from... but it only returns $[0].class which refers to the path of the attribute but not the path of the json object itself... which would be simply $[0]. This is the path i want to receive...


With what other statements may i find the path of the json object inside the array based on either its attribute or value ?
mysql sql json
1 Answers

I tested:

mysql> set @j = '...your json example...';

mysql> select substring_index(json_unquote(json_search(@j, 'one', 'Identity')), '.', 1) as obj;
+------+
| obj  |
+------+
| $[1] |
+------+

Storing data in JSON when you want to use SQL expressions to search for specific sub-fields of the JSON is always going to be harder than storing data in a normal fashion.

Bill_Karwin_208600
Related