mysql - 检查mysql表中json的json_contains

Question

我的表中有 json 数据，其中再次包含 json 值，如下例数据

这是laravel审计，审计表new_values列(类型:text)记录但对于这个特殊情况，我没有使用 Eloquent 或查询生成器，它只是一个原始查询

{"video_status_ids":"[6,8,14,29]"}

现在我想检查一下里面是否有 8。

我试过了

select * from audits WHERE JSON_CONTAINS(new_values, '8', '$.video_status_ids')

和

JSON_CONTAINS(audits.new_values, CAST(' . $status . ' AS json), "$.video_status_ids")';

我尝试json_Search来

1. 从审核中选择 * WHERE JSON_SEARCH(new_values, 'all', '8', 'video_status_ids', '$[*]');

2. 从审核中选择 * WHERE JSON_SEARCH(new_values, 'all', '8', NULL, '$[video_status_ids.*]');

3. SELECT * FROM 审核 WHERE JSON_SEARCH(new_values, 'all', '8', NULL, '$.video_status_ids.*')

获取0条记录

以及其他一些查询，但无法得到预期的结果。

引用this但我得到了 0 条记录。

Answer 1

您的问题是 JSON 数据中的“数组”实际上是一个字符串(请注意它周围的双引号)。因此，要在其中进行搜索，您首先需要 JSON_EXTRACT，然后 JSON_UNQUOTE:

SELECT JSON_CONTAINS(JSON_UNQUOTE(JSON_EXTRACT(new_values, '$.video_status_ids')), '8')
FROM audits

您还可以使用 ->> 表示 JSON_UNQUOTE(JSON_EXTRACT()):

SELECT JSON_CONTAINS(new_values->>'$.video_status_ids', '8')
FROM audits

Demo on dbfiddle

工作解决方案

SELECT * FROM audits WHERE JSON_CONTAINS(new_values->>'$.video_status_ids', '8')