Path to json object which contains an attribute or value

Question

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 ?

Bill Karwin · Accepted Answer · 2021-09-20 15:13:54Z

1

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.

answered Sep 20, 2021 at 15:13

Bill Karwin

567k87 gold badges710 silver badges870 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

genaray Over a year ago

Thanks a lot... been easier than i thought :D I hoped theres a build in function which wraps those three statements...

Bill Karwin Over a year ago

So how are you going to tell the difference between this JSON object and another JSON object that has the value 'Identity' but not in the 'class' field?

genaray Over a year ago

This will never happen :) Each "object" is plain data only and wont contain references to other json objects.... and each of those json objects only exist once per array

Bill Karwin Over a year ago

Remember this conversation when it does happen. :-)

genaray Over a year ago

I will :D in case i can check if the attribute fits...

Collectives™ on Stack Overflow

Path to json object which contains an attribute or value

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related