当前位置：首页 > article >正文

mysql使用sql函数对json数组的处理

article 2024/11/15 11:38:11

MySQL从5.7版本开始增加了对JSON数据类型的支持。你可以使用->>操作符和JSON_EXTRACT函数来访问JSON数据中的值。

但是，对于JSON数组，如果你想要获取数组中的所有元素，MySQL并没有直接的函数来返回数组中的所有元素作为单独的行。不过，你可以通过编写一个自定义的函数或使用应用程序逻辑来遍历数组。

然而，对于简单的场景，比如只获取数组的第一个元素，你可以这样做：

SELECT JSON_UNQUOTE(JSON_EXTRACT(json_column, '$[0]')) AS first_element  
FROM my_table;

再举下实际的例子：

像数据库里存的是以上这种数据，sql语句如下：

SELECT id,JSON_UNQUOTE(JSON_EXTRACT(price, '$[0]')) AS first_element,JSON_UNQUOTE(JSON_EXTRACT(price, '$[1]')) AS first_element  
FROM g_user_demand;

查询结果如下：

下面我再使用另外一种复杂的方法，在网上找的多表联查

1、提取json数组的数值，如:[8000,10000]，还有一种格式：["8000","10000"]

2、假设您有一个表 your_table，其中有一个字段 range_field 存储了类似 ["8000","10000"] 的字符串。假设您还有一个表 another_table，其中存储了要value_field字段检查的值，

3、您可以执行以下查询来检查 another_table 中的 value_field 是否在 your_table 中提取的范围内：

SELECT 
    a.id AS another_table_id,
    a.value_field,
    y.id AS your_table_id,
    y.range_field
FROM 
    another_table a
JOIN 
    your_table y
WHERE 
    a.value_field BETWEEN 
        CAST(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(y.range_field, ',', 1), '"', -1), '[', '') AS UNSIGNED) AND
        CAST(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(y.range_field, ',', -1), '"', -1), ']', '') AS UNSIGNED);

4、解释

CAST(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(range_field, ',', 1), '[', -1), ']', '') AS UNSIGNED) AS min_value,
    CAST(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(range_field, ',', -1), ']', 1), ']', '') AS UNSIGNED) AS max_value