MySQL5.7支持的json字段查询

我会带着你远行 2023-10-06 18:14 39阅读 0赞

#### MySQL5.7支持的json字段查询 ####

*  使用示例
 *   *  1.以json字段作为查询条件：\[json字段全部是JSONObject类型的\]
     *  2.判断JSON字段，是JSONObject类型还是JSONArray类型
     *  附录
     *  3.查询JSON字段是 Array的JSON类型的
     *  4.以json字段为查询条件\[查询JSONArray\]类型 \[like查询\]
     *  5.以json字段为查询条件\[查询JSONArray\]类型 \[=查询\]
     *  6.以json字段为查询条件\[查询JSONObject\]类型 \[包含特殊符号的KEY的查询，应使用" "双引号扩住\]
     *  7.以json字段为查询条件\[查询JSONObject\]类型 \[key:value value是时间 的 时间区间和=查询\]
     *  8.以json字段为查询条件\[查询JSONObject\]类型 \[key:value value是数值 的 数值的 区间和=查询\]

参考：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

## 使用示例 ##

存储结构如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]

### 1.以json字段作为查询条件：\[json字段全部是JSONObject类型的\] ###

SELECT
        * 
    FROM
        worksheet_data_table_data w 
    WHERE
        w.val -> '$.input_0' LIKE '%0%'

查询结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 1]

### 2.判断JSON字段，是JSONObject类型还是JSONArray类型 ###

SELECT
        w.val,
        JSON_TYPE( w.val ) 
    FROM
        worksheet_data_table_data w

查询结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 2]

### 附录 ###

JSON_ARRAY 生成json数组
    JSON_OBJECT 生成json对象
    JSON_QUOTE 加"号
    JSON_CONTAINS 指定数据是否存在
    JSON_CONTAINS_PATH 指定路径是否存在
    JSON_EXTRACT 查找所有指定数据
    JSON_KEYS 查找所有指定键值
    JSON_SEARCH 查找所有指定值的位置
    JSON_ARRAY_APPEND  指定位置追加数组元素
    JSON_ARRAY_INSERT 指定位置插入数组元素
    JSON_INSERT 指定位置插入
    JSON_REPLACE 指定位置替换
    JSON_SET 指定位置设置
    JSON_MERGE 合并
    JSON_REMOVE 指定位置移除
    JSON_UNQUOTE 去"号
    JSON_DEPTH 深度
    JSON_LENGTH 长度
    JSON_TYPE 类型
    JSON_VALID 是否有效json格式

### 3.查询JSON字段是 Array的JSON类型的 ###

SELECT
        w.val,
        JSON_TYPE( w.val ) 
    FROM
        worksheet_data_table_data w
    
    where 
    JSON_TYPE(w.val) = 'ARRAY'

查询结果：  
![在这里插入图片描述][20210705144925146.png]

### 4.以json字段为查询条件\[查询JSONArray\]类型 \[like查询\] ###

SELECT
        * 
    FROM
        worksheet_data_table_data w 
    WHERE
        w.val -> '$[*].*' LIKE '%峰%'

解释：

w.val -> '$[*].*' LIKE '%峰%'
    
    1.w.val字段是JSON数组[无所谓JSONObject还是JSONArray]
    [反正如果按照对象查，那数组是匹配不到的]
    [反之，如果按照数组查，对象也是匹配不到的]
    
    
    2.'$[*].*' 
    代表查询JSONArray 数组格式的JSON字符串中，
    第一个*  代表任意下标
    第二个* 代表任意属性
    
    
    3.LIKE '%峰%'
    匹配条件和正常sql 查询条件一样 写

查询结果如下：  
![在这里插入图片描述][20210705145145552.png]

### 5.以json字段为查询条件\[查询JSONArray\]类型 \[=查询\] ###

等于查询不同于like的查询，需要在外面包裹一层JSON\_CONTAINS（）

比如数据集如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 3]  
想要 查询 出 name字段 既等于"亚瑟" 又等于"jj"的json字段

SELECT
        id,
        `dept-user_0`
    FROM
        worksheet_data_30  d
    WHERE
        JSON_CONTAINS( d.`dept-user_0`->'$[*].name' ,  '"jj"', '$')
        
        AND
        
        JSON_CONTAINS( d.`dept-user_0`->'$[*].name' ,  '"亚瑟"', '$')

查询结果如下：  
![在这里插入图片描述][20210705145333150.png]

### 6.以json字段为查询条件\[查询JSONObject\]类型 \[包含特殊符号的KEY的查询，应使用" "双引号扩住\] ###

json字段值如下格式：

{
        
    "input_0":"lio",
    "textarea_0":"多行文本",
    "input-number_0":"17",
    "date_0":"2019-08-07 09:33:06",
    "select_0":",张三,李四,王五,",
    "area_0":"qwdq4d8q4d8q4wd4",
    "location_0":"48444",
    "file_0":"451515151",
    "dept-user_0":"[{\"id\":\"1\",\"name\":\"jz\"},{\"id\":\"2\",\"name\":\"盖伦\"},{\"id\":\"3\",\"name\":\"jj\"}]",
    "dept-base_0":"[{\"id\":\"1\",\"name\":\"pj\"},{\"id\":\"2\",\"name\":\"游侠\"},{\"id\":\"3\",\"name\":\"jj\"}]"
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 4]  
查看正常字段，可以这么写：

SELECT
        val,
        val -> '$.input_0'
    FROM
        worksheet_data_table_data  
    WHERE
        val -> '$.input_0' like '%o%'

![在这里插入图片描述][20210705145510474.png]  
在对于数据库非JSON字段的列名查询，如果有特殊符号的，可以使用\`\`扩住，例如：

SELECT
    `col_name`,
        val
    FROM
        worksheet_data_table_data  
    WHERE
        `col_name` = 'table_0'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 5]  
但在查询JSON字段时候，指定key时有特殊符号，就不能使用\`\` 反单引号扩住了，而应该采用""双引号 扩住KEY查询：

错误写法：

SELECT
        val,
        val -> '$.`dept-user_0`'
    FROM
        worksheet_data_table_data  
    WHERE
        val -> '$.`dept-user_0`' like '%盖伦%'

报错：Invalid JSON path expression. The error is around character position 15.  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 6]  
正确写法：

SELECT
        val,
        val -> '$."dept-user_0"'
    FROM
        worksheet_data_table_data  
    WHERE
        val -> '$."dept-user_0"' like '%盖伦%'

结果：  
![在这里插入图片描述][20210705145810652.png]

### 7.以json字段为查询条件\[查询JSONObject\]类型 \[key:value value是时间 的 时间区间和=查询\] ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 7]  
【注意：】

对于JSON字符串中的时间格式的存储，需要统一格式，要么都是"%Y-%m-%d" ， 要么都是“%Y-%m-%d %H:%i:%s” 或者其他，但只有保证时间存入的格式是一致的，

才能使用 字符串转时间的函数 ，按照统一的时间格式 进行转化，否则转换不成功，即不能准确查询出结果。

STR\_TO\_DATE(val -> ‘$.date\_0’,’"%Y-%m-%d %H:%i:%s"’)

对于时间的区间查询：\[需要在区间结束时间往后算1天，这样才能保证查询的准确性\]

SELECT
        val,
        val -> '$."date_0"'
    FROM
        worksheet_data_table_data  
    WHERE
        STR_TO_DATE(val -> '$.date_0','"%Y-%m-%d %H:%i:%s"')  between '2019-08-07 10:33:06' AND date_add('2019-08-08', interval 1 day)

![在这里插入图片描述][20210705150222817.png]  
对于时间的=查询：\[其实也是用between and，只不过，区间结束时间往后算1天，这样得到的就是今天到明天之前的所有时间数据\]

![在这里插入图片描述][20210705150323909.png]

### 8.以json字段为查询条件\[查询JSONObject\]类型 \[key:value value是数值 的 数值的 区间和=查询\] ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 8]  
数值的区间查询【between 或者 > < 都可以】

SELECT
        val,
        val -> '$."input-number_0"',
        CAST(val -> '$."input-number_0"'  AS   DECIMAL(18,3))
    FROM
        worksheet_data_table_data  
    WHERE
        CAST(val -> '$."input-number_0"'  AS   DECIMAL(18,3)) between 16 and 18

![在这里插入图片描述][20210705150523972.png]  
数值=查询

![在这里插入图片描述][20210705150549163.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210705144129911.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210705144312571.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210705144459956.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[20210705144925146.png]: https://img-blog.csdnimg.cn/20210705144925146.png
[20210705145145552.png]: https://img-blog.csdnimg.cn/20210705145145552.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210705145241759.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[20210705145333150.png]: https://img-blog.csdnimg.cn/20210705145333150.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20210705145424146.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[20210705145510474.png]: https://img-blog.csdnimg.cn/20210705145510474.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210705145546516.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20210705145711265.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[20210705145810652.png]: https://img-blog.csdnimg.cn/20210705145810652.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/2021070515011362.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[20210705150222817.png]: https://img-blog.csdnimg.cn/20210705150222817.png
[20210705150323909.png]: https://img-blog.csdnimg.cn/20210705150323909.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20210705150406969.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[20210705150523972.png]: https://img-blog.csdnimg.cn/20210705150523972.png
[20210705150549163.png]: https://img-blog.csdnimg.cn/20210705150549163.png