Query specific text inside json text data in MYSQL

Question

I'd like to query from the reviewed_by table below where the "company" is "AAA" and "review" is "Need Review" Here's mysql table :

+-----------+
| DATA_TYPE |
+-----------+
| text      |
+-----------+

+-------------------------+
| reviewed_by             |
+-------------------------+
|[{"company":"AAA","review":"OK","reviewed_at":"2021-01-26 08:59:26"}]|
|[{"company":"BBB","review":"OK","reviewed_at":"2021-01-26 08:59:26"}]|
|[{"company":"AAA","review":"Need Review","reviewed_at":"N\/A"}]|
+-------------------------+

Here's the #1 query i've tried :

SELECT * FROM `t_transaction` 
WHERE `reviewed_by` 
LIKE '%`"company":"AAA","review":"Need Review"`%'

Here's the #2 query i've tried :

SELECT * FROM `t_transaction` 
WHERE `reviewed_by` 
LIKE '%"company":"AAA","review":"Need Review"%'

ci3 query :

$like = ['reviewed_by','"company":"AAA","review":"Need Review"'];
$this->db->select('*')   
         ->from('t_transacion')
         ->group_by('id')
         ->like($like[0],$like[1]);

The result i've got from those 2 queries was nothing, How can i do this type of query (and also if using codeigniter 3) ?

#2 (without the backticks) should work. If it doesn't, it's likely not the `LIKE`... — Benni, Jan 27 '21 at 08:22
@Benni do you mean by Query #2 above ? already tried it and got nothing — owf, Jan 27 '21 at 08:31
I tested the exact query (in plain MySql, not codeigniter), and it worked fine — Benni, Jan 27 '21 at 08:34
MySQL can query inside JSON text, for example: ```SELECT * FROM 't_transaction' WHERE reviewed_by->>'$.company' = 'AAA' AND reviewed_by->>'$.review' = 'Need Review';``` — Eyad Mohammed Osama, Jan 27 '21 at 10:35
If you have any control over the database design, don't store JSON under a single column, rather make all those JSON keys a separate column. It will make your searches not only easier, but much more efficient too. — El_Vanja, Jan 27 '21 at 12:24

score 3 · Accepted Answer · answered Jan 27 '21 at 21:06

MySql has some functions that allow you to do search over a json field. See documentation.

The reviewed_by column is a json array and you want to seach the first element of that array. Using the function JSON_EXTRACT you can extract data from the json field. In your case to get the json in the first position in the array so we execute JSON_EXTRACT(reviewed_by, '$[0]') which will return {"company":"...","review":"..","reviewed_at":"..."}. From the returned json we can call again the JSON_EXTRACT function to get a value given a key. If we select JSON_EXTRACT(JSON_EXTRACT(reviewed_by, '$[0]'), "$.company") this will return the company value from inside the json.

There are different ways to select what you want. I will give you two option and they have pros and cons. Take a look at this stackoverflow.

First approach using the where clause:

SELECT reviewed_by
FROM t_transaction
WHERE JSON_EXTRACT(JSON_EXTRACT(reviewed_by, '$[0]'), "$.company") = "AAA"
  AND JSON_EXTRACT(JSON_EXTRACT(reviewed_by, '$[0]'), "$.review") = "Need Review";

Second approach using the having clause:

SELECT JSON_EXTRACT(reviewed_by, '$[0]') AS json
FROM t_transaction
HAVING json -> "$.company" = "AAA"
   AND json -> "$.review" = "Need Review";

Query specific text inside json text data in MYSQL

1 Answers1