CODEWITHSUNDEEP
phpmysqljsoncodeigniter
owf
owf

Reputation: 251

Query specific text inside json text data in MYSQL

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) ?

Upvotes: 0

Views: 57

Answers (1)

Francisco de Castro
Francisco de Castro

Reputation: 769

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";

Upvotes: 3

Related Questions