mysql常用json查询
格式化数据格式
-- 格式化DECIMAL
select
CONVERT(
total_money,
DECIMAL(15, 2)
) as convert_money
from
`agent_data`
convert_money|
-------------+
8840.00|
0.00|
-- 格式化时间
select
CONVERT(created_at, DATE) as created_at,
CONVERT(created_at, DATETIME) as created_at2
from
`agent_data`
created_at|created_at2 |
----------+-------------------+
2022-05-27|2022-05-27 15:22:59|
2022-05-27|2022-05-27 15:31:10|
保留小数位数,多的截取
select
TRUNCATE(total_money, 2) as truncate_money
from
`agent_data`
truncate_money|
--------------+
8840|
IFNULL 判断
-- 在SUM等计算的时候,如果任何参数为null,那么计算的结果也将会是null,是不对的,需要手动判断
select
SUM(IFNULL(total_money, 0)) as sum_total_money
from
`agent_data`
sum_total_money|
---------------+
10491|
IF 判断
-- 如果type==1或者==2统计total_money字段否则统计money字段
select
SUM(
IF(
`type` = 1
OR `type` = 2,
`total_money`,
`money`
)
) as `sum_total_money`
from
`agent_data`
sum_total_money|
---------------+
9715|
逗号分割的文本数据进行查询
-- FIND_IN_SET(needle,haystack);
-- 第一个参数needle是要查找的字符串。第二个参数haystack是要搜索的逗号分隔的字符串列表。
select
`remark_name`,
FIND_IN_SET("员工备注2", `remark_name`) as find_num
from
`agent_data`
WHERE
FIND_IN_SET("员工备注2", `remark_name`) > 0
remark_name|find_num|
-----------+--------+
员工备注2 | 1|
json格式读取
-- 索引json数据值查询
select
rule_key,
JSON_EXTRACT(rule_key, '$[0]') as json_data_1
from
`agent_data`
WHERE
JSON_EXTRACT(rule_key, '$[0]') = 'force_popup'
rule_key |json_data_1 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
["force_popup"] |"force_popup"|
["force_popup"] |"force_popup"|
["force_popup", "fixed_profit", "operating_amortization", "view_orders"] |"force_popup"|
["force_popup", "view_order", "view_order_mobile_number_display"] |"force_popup"|
-- 键值对json数据值查询
select
`compute_arr`,
json_extract(`compute_arr`, '$."first"') as json_first,
json_extract(`compute_arr`, '$."first"."3"') as json_first_3
from
`order`
WHERE
json_extract(`compute_arr`, '$."first"') IS NOT NULL
compute_arr |json_first |json_first_3|
-----------------------------------------------------------------------------------------------------+----------------------------------------------------+------------+
{"first": {"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}, "second": {"3": 1, "80": 0, "81": 0}}|{"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}|-480 |
{"first": {"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}, "second": {"3": 1, "80": 0, "81": 0}}|{"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}|-480 |
-- 索引包含键值对json数据值查询
select
`data`,
json_extract(`data`, '$[*]."field"') as `field_arr`
from
`agent_log`
WHERE
id = 106
data |field_arr |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+
[{"field": "equipment_amortization_all_money", "title": "金额", "value": "2.00", "oldvalue": "1.00"}, {"field": "equipment_amortization_day_money", "title": "设备每天的金额", "value": "3.00", "oldvalue": "2.00"}]|["equipment_amortization_all_money", "equipment_amortization_day_money"]|
json匹配
-- JSON_CONTAINS() 函数
-- target_json必需的。一个 JSON 文档。candidate_json 必需的。被包含的 JSON 文档。 path 可选的。一个路径表达式。
-- 匹配json,索引类型: 直接匹配对应的value即可
select
rule_key,
JSON_CONTAINS(
rule_key,
'["fixed_profit", "operating_amortization"]'
) as check_num,
JSON_CONTAINS(
rule_key,
'["fixed_profit"]'
) as check_num2
from
`agent_data`
WHERE
id = 39
rule_key |check_num|check_num2|
------------------------------------------------------------------------+---------+----------+
["force_popup", "fixed_profit", "operating_amortization", "view_orders"]| 1| 1|
-- 匹配json,键值对类型: 对 compute_arr.first.3 的值进行匹配, 注意-480的数值类型需要一致
select
`compute_arr`,
`compute_arr` -> '$."first"."3"',
JSON_CONTAINS(
`compute_arr` -> '$."first"."3"',
'-480'
) as check_num
from
`order`
compute_arr |`compute_arr` -> '$."first"."3"'|check_num|
-----------------------------------------------------------------------------------------------------+--------------------------------+---------+
{"first": {"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}, "second": {"3": 1, "80": 0, "81": 0}}|-480 | 1|
{"first": {"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}, "second": {"3": 1, "80": 0, "81": 0}}|-480 | 1|
-- 匹配json,索引包含键值对类型: 索引包含键值对json数据值查询
select
`data`,
JSON_CONTAINS(
`data` -> "$[*].field",
'["equipment_amortization_all_money"]'
) as check_num
from
`agent_log`
WHERE
id = 106
data |check_num|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+
[{"field": "equipment_amortization_all_money", "title": "设备金额", "value": "2.00", "oldvalue": "1.00"}, {"field": "equipment_amortization_day_money", "title": "设备每天金额", "value": "3.00", "oldvalue": "2.00"}]| 1|
分组查询之后获取对应分组的字段的所有值
select `key`, `type` from `rule`;
key |type |
--------------------------------+-----------------+
force_popup |agent |
no_withdrawal_privileges |agent |
order_details |agent |
lending |agent |
no_withdrawal_privileges |employee |
force_popup |employee |
lend_charging_privileges |employee |
view_order |employee |
select `key`, `type`,GROUP_CONCAT(`key`) from `rule` GROUP BY `type`;
key |type |type_all_key
------------------------+-----------------+----------------------------------------------
force_popup |agent |force_popup,no_withdrawal_privileges,order_det
no_withdrawal_privileges|channeler |no_withdrawal_privileges,order_info
order_info |customer_servicer|order_info
no_withdrawal_privileges|employee |no_withdrawal_privileges,force_popup,lend_char
order_info |logisticser |order_info
no_withdrawal_privileges|outlets |no_withdrawal_privileges,no_login,order_info
json 数组操作新增和删除
/*
原数据:
["tixian_weixin", "dingdan_mx", "tixian_zfb"]
新增: 查询第一个匹配的字符串, 为null就新增
*/
UPDATE
outlets
SET
rule_key = IF(JSON_SEARCH(IFNULL(rule_key, '[]'),
'one',
'tixian_zfb') IS NULL,
JSON_MERGE_PRESERVE(IFNULL(rule_key, '[]'),
'["tixian_zfb"]'),
rule_key)
where
id = 1
/* 删除 */
UPDATE
outlets
SET
rule_key = IF(JSON_SEARCH(IFNULL(rule_key, '[]'),
'one',
'tixian_zfb') IS NULL,
rule_key,
JSON_REMOVE(rule_key,
JSON_UNQUOTE(JSON_SEARCH(IFNULL(rule_key, '[]'),
'one',
'tixian_zfb'))))
where
id = 1