问题

我有以下的表格：

```sql
CREATE TABLE mytable (
    id     int, 
    name   varchar(255), 
    data   json
);

其中 data 列具有 JSON 类型，并且我想选择字段 data 等于字符串 ["a", "b"] 的行。

我目前正在使用的简单方法是：

SELECT * FROM mytable WHERE data = '["a", "b"]'

然而，它总是返回空集。什么是正确比较 JSON 字段与字符串的方法？

<details>
<summary>英文:</summary>

I have following table:

CREATE TABLE mytable (
id int,
name varchar(255),
data json
);

| id | name | data            |
|----|------|-----------------|
| 1  | John | ["a", "b"]      |
| 2  | Pete | ["a", "b", "c"] |
| 3  | Mike | ["a", "b"]      |

Where column `data` has `JSON` type, and I want to select rows where field `data` equal to string `["a", "b"]`.

Naive approach I'm currently using:

```sql
SELECT * FROM mytable WHERE data = '["a", "b"]'

Yet it always returns empty set. What is right way to compare JSON fields with strings?

答案1

得分: 2

使用JSON_ARRAY()函数创建一个JSON数组，然后与之比较。

SELECT * FROM mytable WHERE data = JSON_ARRAY('a', 'b');

演示

SELECT * FROM mytable WHERE data = JSON_ARRAY('a', 'b');

答案2

得分: 2

你可以使用CAST(... AS JSON)将JSON字符串转换为JSON数据类型以进行比较：

SELECT *
FROM t
WHERE data = CAST('[
  "a",
  "b"
]' AS JSON)

SELECT *
FROM t
WHERE data = CAST('[
  "a",
  "b"
]' AS JSON)