MySQL - JSON_CONTAINS 不适用于带引号或逗号分隔字符串的数组？

Question

我有一个包含 json 列的表，我在其中存储了契约(Contract)类型的 ID 列表。我想在 contract_types 表上进行联接，以便我可以获得契约(Contract)类型名称的串联列表及其 ID。

CREATE TABLE `my_alerts` (
   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(255) NOT NULL,
   `criteria` JSON NULL DEFAULT NULL,
   PRIMARY KEY (`id`)
)


CREATE TABLE `contract_types` (
   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(255) NOT NULL,
   PRIMARY KEY (`id`)
)

INSERT INTO contract_types (id, name) VALUES (1, 'Full time');
INSERT INTO contract_types (id, name) VALUES (2, 'Part time');

INSERT INTO my_alerts (id, name, criteria) VALUES (1, 'test', '{"contractTypes": ["1", "2"]}');

我尝试了以下查询，但它不起作用:

SELECT 
   a.id, 
   a.name, 
   GROUP_CONCAT(c.name SEPARATOR ', ') as contractTypes
FROM my_alerts a
LEFT JOIN contract_types c on JSON_CONTAINS(a.criteria, CAST(c.id as JSON), '$.contractTypes')
group by a.id

如果我更改 json 列并存储不带引号的数组值，我只能使查询工作:

{"contractTypes": [1, 2]}

不幸的是，我无法更改数组值的存储方式，即不带引号。

当 json 数组值以以下两种格式存储时，如何使上述 json_contains 查询起作用:

{"contractTypes": ["1", "2"]}

或

{"contractTypes": ["1,2"]}

* 更新 *

如果 json 是第二种格式，即逗号分隔列表，我已经想出了如何加入，如下所示:

LEFT JOIN contract_types c on find_in_set(c.id, JSON_EXTRACT(a.criteria, '$.contractTypes'))

现在，当数组值带有双引号时，我只需要一些连接方面的帮助。

Answer 1

我已经弄清楚了这个问题。

如果您的 json 列名为“user_ids”并且它具有如下值:

["1", "2", "3", "4", "5"] 

你可以这样做

SELECT JSON_CONTAINS(user_ids, '"2"', '$') as u;

'u' 将返回 1。

此解决方案的关键是确保先用双引号将数字括起来，然后用单引号括起来。