在MYSQL中查询json文本数据中的特定文本

Question

我想查询从reviewed_by表下面的"company"是"AAA"one_answers";review"是"需要回顾";下面是mysql表:

+-----------+
| 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"}]|
+-------------------------+

这是我尝试过的#1查询:

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

这是我尝试过的#2查询:

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]);

我从这2个查询得到的结果是什么都没有，我怎么能做这种类型的查询(也如果使用codeigniter 3) ?

Answer 1

MySql有一些函数允许您在json字段上进行搜索。看到文档。

reviewed_by列是一个json数组，您想要搜索该数组的第一个元素。使用JSON_EXTRACT函数可以从json字段中提取数据。在您的情况下，要在数组的第一个位置获取json，因此我们执行JSON_EXTRACT(reviewed_by, '$[0]')，这将返回{"company":"...","review":"..","reviewed_at":"..."}。从返回的json中，我们可以再次调用JSON_EXTRACT函数来获得给定键的值。如果我们选择JSON_EXTRACT(JSON_EXTRACT(reviewed_by, '$[0]'), "$.company")，这将返回json.

内部的公司值。有不同的方法来选择你想要的。我会给你两个选择，它们都有优点和缺点。看看这个stackoverflow。

使用where子句的第一种方法:

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

第二种使用having从句的方法:

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