CODEWITHSUNDEEP
phpsqljson
Shogu23
Shogu23

Reputation: 121

Search function SQL in json array

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?

Upvotes: 0

Views: 32

Answers (1)

Shogu23
Shogu23

Reputation: 121

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.

Upvotes: 1

Related Questions