Mysql 数据库json查询

https://www.cnblogs.com/sxdcgaq8080/p/10876745.html

参考：https://www.cnblogs.com/ooo0/p/9309277.html

参考：https://www.cnblogs.com/pfdltutu/p/9019444.html

使用示例：

存储结构如下：

image

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

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT
*
FROM worksheet_data_table_data w WHERE w.val -> '$.input_0' LIKE '%0%'</pre>

查询结果：

image

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

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT w.val,
JSON_TYPE( w.val ) FROM worksheet_data_table_data w</pre>

查询结果：

image

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

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT w.val,
JSON_TYPE( w.val ) FROM worksheet_data_table_data w where JSON_TYPE(w.val) = 'ARRAY'</pre>

[ 复制代码

](javascript:void(0); "复制代码")

查询结果：

image

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

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT
*
FROM worksheet_data_table_data w WHERE w.val -> '$[].' LIKE '%峰%'</pre>

解释：

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">w.val -> '$[].' LIKE '%峰%'

1.w.val字段是JSON数组[无所谓JSONObject还是JSONArray]
[反正如果按照对象查，那数组是匹配不到的]
[反之，如果按照数组查，对象也是匹配不到的]

2.'$[].' 代表查询JSONArray 数组格式的JSON字符串中，
第一个* 代表任意下标
第二个* 代表任意属性 3.LIKE '%峰%' 匹配条件和正常sql 查询条件一样 写</pre>

[ 复制代码

](javascript:void(0); "复制代码")

查询结果如下：

image

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

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

比如数据集如下：

image

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

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT id,
dept-user_0 FROM worksheet_data_30 d WHERE JSON_CONTAINS( d.dept-user_0->'[*].name' , '"亚瑟"', '/pre>)</pre>

[ 复制代码

](javascript:void(0); "复制代码")

查询结果如下：

image

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

json字段值如下格式：

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">{
"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"}]"
}</pre>

[ 复制代码

](javascript:void(0); "复制代码")

image

查看正常字段，可以这么写：

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT val,
val -> '.input_0' like '%o%'</pre>

[ 复制代码

](javascript:void(0); "复制代码")

image

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

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT col_name,
val FROM worksheet_data_table_data WHERE col_name = 'table_0'</pre>

[ 复制代码

](javascript:void(0); "复制代码")

image

但在查询JSON字段时候，指定key时有特殊符号，就不能使用`` 反单引号扩住了，而应该采用""双引号 扩住KEY查询：

错误写法：

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT val,
val -> '.dept-user_0' like '%盖伦%'</pre>

[ 复制代码

](javascript:void(0); "复制代码")

报错：Invalid JSON path expression. The error is around character position 15.

image

正确写法：

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT val,
val -> '."dept-user_0"' like '%盖伦%'</pre>

[ 复制代码

](javascript:void(0); "复制代码")

结果：

image

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

image

【注意：】

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

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

STR_TO_DATE(val -> '$.date_0','"%Y-%m-%d %H:%i:%s"')

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

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT val,
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)</pre>

[ 复制代码

](javascript:void(0); "复制代码")

image

对于时间的=查询：[其实也是用between and，只不过，区间结束时间往后算1天，这样得到的就是今天到明天之前的所有时间数据]

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT val,
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)</pre>

[ 复制代码

](javascript:void(0); "复制代码")

image

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

image

数值的区间查询【between 或者 > < 都可以】

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT val,
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</pre>

[ 复制代码

](javascript:void(0); "复制代码")

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT val,
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</pre>

[ 复制代码

](javascript:void(0); "复制代码")

image

数值=查询

[ 复制代码

](javascript:void(0); "复制代码")

<pre style="margin: 0px; padding: 0px; overflow-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">SELECT val,
val -> '."input-number_0"' AS DECIMAL(18,3)) FROM worksheet_data_table_data WHERE
CAST(val -> '$."input-number_0"' AS DECIMAL(18,3)) =16</pre>

[ 复制代码

](javascript:void(0); "复制代码")

image