如何在MySQL中搜索JSON数组? [英] How to search JSON array in MySQL?

问题描述

假设我在某些MySQL表中有一个名为 data 的JSON列，并且该列是单个 array .因此，例如，数据可能包含:

Let's say I have a JSON column named data in some MySQL table, and this column is a single array. So, for example, data may contain:

[1,2,3,4,5]

[1,2,3,4,5]

现在，我想选择所有具有数据列的行，其中数据列的数组元素之一大于2.这可能吗?

Now I want to select all rows which have a data column where one of its array elements is greater than 2. Is this possible?

我尝试了以下操作，但无论数组中的值如何，它似乎总是 true :

I tried the following, but seems it is always true regardless of the values in the array:

SELECT * from my_table
WHERE JSON_EXTRACT(data, '$[*]') > 2;

推荐答案

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "2") is not null 

是真的

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "6") is not null

是错误的

这篇关于如何在MySQL中搜索JSON数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！