英文:
Comparing JSON-typed column with specific string value
问题
我有以下的表格：```sqlCREATE 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"] |
其中 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:```sqlSELECT * 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');
英文:
Use the JSON_ARRAY() function to create a JSON array, and compare with this.
SELECT * FROM mytable WHERE data = JSON_ARRAY('a', 'b');
答案2
得分: 2
你可以使用CAST(... AS JSON)将JSON字符串转换为JSON数据类型以进行比较：
SELECT *FROM tWHERE data = CAST('["a","b"]' AS JSON)
英文:
You can use CAST(... AS JSON) to convert the JSON-as-string to JSON data type for comparison:
SELECT *FROM tWHERE data = CAST('["a","b"]' AS JSON)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论