MySQL，根据JSON数组中的值选择记录 [英] MySQL, Select records based on values in JSON array

问题描述

我在处理MySQL中的JSON字段方面还很陌生.我遇到的所有解决方案都处理具有键/值的对象.无法找到处理JSON数组的对象.

I'm still pretty new to handling JSON fields in MySQL. All the solutions I've come across deal with objects that have key/values; unable to find one that handles JSON arrays.

无论如何，我要做的是能够选择interestIds中包含2的所有行.我怎么做?谢谢.

Anyways, what I want to do is to be able to select all rows where the interestIds contain 2 in them. How do I do that? Thanks.

用户表

+----+-------------+
| id | interestIds |
+----+-------------+
|  1 | [1, 2]      |
|  2 | [3, 2]      |
|  3 | [2, 4]      |
+----+-------------+

示例测试查询:

SET @userId = 2;
SELECT * FROM Users
WHERE @userId IN JSON_CONTAINS(@user, interestIds, '$[1]');

我对如何使用JSON_*函数感到困惑；不知道要为第三个参数输入什么...

I am confused as how to use the JSON_* functions; not sure what to put for the 3rd parameter...

推荐答案

您可以使用

You can use the following solution, using JSON_CONTAINS:

SELECT * 
FROM Users
WHERE JSON_CONTAINS(interestIds, '2') = 1;

第三个(可选)参数path使您可以仅在JSON值的特定部分上使用此功能.因此，以下示例检查2是否为数组的第二个值:

The third (optional) paramater path gives you the posibility to use this function only on a specific part of your JSON value. So the following example checks if 2 is the second value of the array:

SELECT *
FROM test
WHERE JSON_CONTAINS(interestIds, '2', '$[1]') = 1;

在dbfiddle.uk上的演示

这篇关于MySQL，根据JSON数组中的值选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！