搜索 | 会员  
mysql5.7支持的json字段查询【mybatis】
来源: 博客园   作者:网友  日期:2019-8-26  类别:编程语言  主题:JAVA  编辑:Dwight
1.以json字段作为查询条件:[json字段全部是JSONObject类型的]

使用示例:

存储结构如下:

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

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

查询结果:

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

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

查询结果:

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

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

查询结果:

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 查询条件一样 写

查询结果如下:

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

等于查询不同于like的查询,需要在外面包裹一层JSON_CONTAINS()

比如数据集如下:

 想要 查询 出 name字段  既等于"亚瑟"  又等于"jj"的json字段

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

查询结果如下:

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\"}]"
}

 查看正常字段,可以这么写:

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

在对于数据库非JSON字段的列名查询,如果有特殊符号的,可以使用``扩住,例如:

复制代码

SELECT`col_name`,
   valFROM
   worksheet_data_table_data  
WHERE
   `col_name` = 'table_0'

复制代码

但在查询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.

正确写法:

复制代码

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

复制代码

结果:

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

【注意:】

对于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)

复制代码

 

对于时间的=查询:[其实也是用between and,只不过,区间结束时间往后算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-07', interval 1 day)

复制代码

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

数值的区间查询【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

复制代码

复制代码

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)) >16.3

复制代码

数值=查询

复制代码

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)) =16

复制代码

德仔网尊重行业规范,每篇文章都注明有明确的作者和来源;德仔网的原创文章,请转载时务必注明文章作者和来源:德仔网;
头条那些事
大家在关注
我们的推荐
也许感兴趣的
干货
了解一下吧