MySQL-JSON函数实操积累

1、 SELECT CAST('[]' as JSON)

2、 SET @json = '{"a":"avalue","barr":[1,2]}';
SELECT JSON_INSERT(@json, '$.newkey', '[1]','$.newkey2','{test:1}','$.newkey3','[]'); -- 有key 则不会重复执行

3、SELECT (CASE
WHEN (id_from = (SELECT pk_id from test_json WHERE id_own_org = 9999 and package_name = '预检单') ) THEN
JSON_INSERT(package_detail, '$.packageType', 'YJ','$.businessLabels',CAST('[]' as JSON),'$.hasAdditionalCheck',0)
ELSE
JSON_INSERT(package_detail, '$.packageType', 'JC','$.businessLabels',CAST('[]' as JSON),'$.hasAdditionalCheck',0)
END)package_detail,package_type,business_labels,has_additional_check,package_name,id_from from test_json --- 太复杂

4、 json array 搜索
数据库保存记录：
1 []
2 [1, 2, 3, 4, 5]
3 [4, 5, 6, 7, 8, 9]
4 [20, 21, 22]
查询操作： -- '$[*]' 可以换成 '$'
4.1 SELECT JSON_EXTRACT(arr, '$[*]') from array_search WHERE json_contains(arr, '[4,5]'); --关键词部分可以有空格
返回：
[1, 2, 3, 4, 5]
[4, 5, 6, 7, 8, 9]

4.2 模糊匹配
SELECT JSON_EXTRACT(arr, '$[*]') from array_search WHERE REPLACE(JSON_EXTRACT(arr, '$[*]'),' ','') LIKE "%1,2,%";
返回：注意这里是转成无空格的字符串来匹配，需要关注关键词的有序性
[1, 2, 3, 4, 5]

4.3 精确匹配 不适合json array
SELECT JSON_EXTRACT(arr, '$') from array_search WHERE FIND_IN_SET('3',id) -- 被查询的字段在后面
返回：
[4, 5, 6, 7, 8, 9]


参考 https://bbs.csdn.net/topics/391982545?page=1

5、从json中解析出key，解析数array中的第1个对象中的值（查询过程中对象变成了数组）
如果有担心有性能的问题，就流程表的创建时间过滤( >= ) ，
另外注意脏数据，要 在json函数前 加上判断 and faxianren is not null and faxianren <> ''

下面看起来解析的是数组

select faxianren as result from ent_zhongguolvyeguangxif_datapool_yuangongshenbaojifen order by id desc limit 3\G
*************************** 1. row ***************************
result: {"displayValue":"罗宁权","real":{"blacklist":{},"orgs":{"13877636909":{"id":"13877636909","parent_id":"36","Type":"member"}}},"appointId":"36"}

select json_unquote(json_extract(faxianren,'$.real.orgs')) as result from ent_zhongguolvyeguangxif_datapool_yuangongshenbaojifen order by id desc limit 3\G
*************************** 1. row ***************************
result: {"13807864149": {"id": "13807864149", "Type": "member", "parent_id": "244"}}

select json_unquote(json_extract(faxianren,'$.real.orgs.*')) as result from ent_zhongguolvyeguangxif_datapool_yuangongshenbaojifen order by id desc limit 3\G
*************************** 1. row ***************************
（查询过程中对象变成了数组）
result: [{"id": "13807864149", "Type": "member", "parent_id": "244"}]

select json_unquote(json_extract(faxianren,'$.real.orgs.*[0].id')) as result from ent_zhongguolvyeguangxif_datapool_yuangongshenbaojifen order by id desc limit 3\G
*************************** 1. row ***************************
result: ["13807864149"]

select json_unquote(JSON_EXTRACT(json_extract(faxianren,'$.real.orgs.*[0].id'),"$[0]")) as result from ent_zhongguolvyeguangxif_datapool_yuangongshenbaojifen order by id desc limit 3\G
*************************** 1. row ***************************
result: 13807864149



下面看起来解析的是orgs 下的 key
select json_unquote(JSON_KEYS(json_extract(faxianren,'$.real.orgs'))) as result from ent_zhongguolvyeguangxif_datapool_yuangongshenbaojifen order by id desc limit 3\G
*************************** 1. row ***************************
（查询过程中对象变成了数组）
result: ["18977618843"]

select json_unquote(JSON_EXTRACT(JSON_KEYS(json_extract(faxianren,'$.real.orgs')),"$[0]")) as result from ent_zhongguolvyeguangxif_datapool_yuangongshenbaojifen order by id desc limit 3\G
*************************** 1. row ***************************
result: 18977618843