JSON extract from uknknown array key

Question

I have a structure - column visitors

[{"firstname":"john","lastname":"penn"},{"firstname":"mickey","lastname":"smith"},{"firstname":"darth","lastname":"vader"}]

I would like to find out if there is a john among all these people listed inside this json.

My quers does not find anything (no rows found)

SELECT conference_name FROM conference WHERE JSON_EXTRACT(visitors, "$[*].firstname") = 'john';

Is this possible to achieve using just json_extract and NOT json_search since it is very slow with big tables?

@SimonFranzen fixed, to be more clear. Conference is table, visitors is column with json of all the visitors. — Biker John
– Biker John, Commented Mar 6, 2018 at 1:07
change "$[*].firstname" to "$.firstname" . I also added an answer. Can you check if it helps — Simon Franzen
– Simon Franzen, Commented Mar 6, 2018 at 1:13

Simon Franzen · Accepted Answer · 2018-03-06 01:10:35Z

0

Like mentioned in the docs:

mysql> SELECT c, JSON_EXTRACT(c, "$.firstname"), g
 > FROM jemp
 > WHERE JSON_EXTRACT(c, "$.firstname") = 'john';

or in your case you can try

SELECT conference_name FROM conference WHERE JSON_EXTRACT(visitors, "$.firstname") = 'john';

or ?

SELECT conference.conference_name FROM conference WHERE JSON_EXTRACT(conference.visitors, "$.firstname") = 'john';

answered Mar 6, 2018 at 1:10

Simon Franzen

2,72727 silver badges35 bronze badges

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

2 Comments

Biker John Over a year ago

Doesnt work, still 0 rows returned. As you can see in my case, there is a table as a top level group, your solution only works when there is only one of each key - just one json object. My case is practically multiple of same structured objects inside one array - $[object_number_inside_array].objectkey

Simon Franzen Over a year ago

ok wow, you are right. phhheew, searching in a array?? Have you seen this post stackoverflow.com/questions/36346252/…

Collectives™ on Stack Overflow

JSON extract from uknknown array key

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related