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, Mar 06 '18 at 01:07
change `"$[*].firstname"` to `"$.firstname"` . I also added an answer. Can you check if it helps — Simon Franzen, Mar 06 '18 at 01:13

score 0 · Answer 1 · answered Mar 06 '18 at 01:10

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 06 '18 at 01:10

Simon Franzen

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 – Biker John Mar 06 '18 at 01:23
ok wow, you are right. phhheew, searching in a array?? Have you seen this post https://stackoverflow.com/questions/36346252/how-to-extract-rows-from-a-json-array-using-the-mysql-udf-json-extract-0-4-0 – Simon Franzen Mar 06 '18 at 01:25

1 Answers1