John Davidson

Message:

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?

---

My Answer:
SQL database, but now I need to implement it for a JSON array. Can anyone help me with the SQL query for searching in a JSON array?

Here is an example of my JSON array structure:

json
{
"users": [
{
"id": 1,
"name": "John Doe",
"email": "john.doe@example.com"
},
{
"id": 2,
"name": "Jane Smith",
"email": "jane.smith@example.com"
},
{
"id": 3,
"name": "Alice Johnson",
"email": "alice.johnson@example.com"
}
]
}


I want to be able to search for a user by their name or email. Can someone provide me with an SQL query that can achieve this? Thank you in advance!