Search function SQL in json array

Question

Hi everyone (hope the title is right, my english is rusted sometimes)

I did a search function, it work for my "code" infos, my company names, but i need to search for email, that are in a json entry, i did it like this :

    $sql = 'SELECT SQL_CALC_FOUND_ROWS * FROM ' . $this->table . ' AS s 
            WHERE ((s.code LIKE :q)
            OR (s.company_name LIKE :q)
            OR (JSON_EXTRACT(s.contacts, "$.email") LIKE :q))';

Where is my mistake?

Well, can you share example data of the JSON? One row should be sufficient, you can mask the real values, it's the structure that matters. — El_Vanja
– El_Vanja, Commented Nov 24, 2020 at 13:59
sure, [{"lastname":"Bonnot","firstname":"Jean","email":"[email protected]","phone1":"0675421357"}] — Shogu23
– Shogu23, Commented Nov 24, 2020 at 14:24
I think it's failing because your JSON is in an array and not at the "top" level which $.email looks for — dazed-and-confused
– dazed-and-confused, Commented Nov 24, 2020 at 14:54
ok so my email is in the 2nd level, how do i do? maybe i should do a foreach of my "s.contacts" ? — Shogu23
– Shogu23, Commented Nov 24, 2020 at 15:02
Seems like contacts really ought to be another table. Would save you a lot of headaches like this one. — El_Vanja
– El_Vanja, Commented Nov 24, 2020 at 15:05

Shogu23 · Accepted Answer · 2020-11-24 15:07:59Z

1

Ok, so i used this answer :

OR (JSON_EXTRACT(s.contacts, "$**.email") LIKE :q))';

Like @dazed-and-confused pointed my email wasn't at the top level, so i used ** to go to the 2nd level, and it work.

answered Nov 24, 2020 at 15:07

Shogu23

12112 bronze badges

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

3 Comments

dazed-and-confused Over a year ago

The only thing with this solution is that if the JSON string contains multiple arrays, this method will get all of them that match, not just one.

Shogu23 Over a year ago

can't think of another way to do it, any suggestion?

dazed-and-confused Over a year ago

OR (JSON_EXTRACT(s.contacts, "$[0].email") LIKE :q))';

Collectives™ on Stack Overflow

Search function SQL in json array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related