JSON查询demo
业务场景
有一张表 project_users
表的数据列表
openid
------
[{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}]
[{"app_id": "1", "openid": "oromp5QBh9-a8VQlM99knmq2eM7k"}]
(NULL)把表扁平化下来
SELECT
uid
,openid
,openid->'$[*].app_id'
,json_search(openid->'$[*].app_id','all','2')
,openid->'$[0].openid'
,a.app_id
,a.json_openid
FROM `project_users`,JSON_TABLE(openid, '$[*]' COLUMNS(app_id INT path '$.app_id',json_openid VARCHAR(50) path '$.openid')) a
-----
uid openid openid->'$[*].app_id' json_search(openid->'$[*].app_id','all','2') openid->'$[0].openid' app_id json_openid
--------- ---------------------------------------------------------------------------------------------------------------------- --------------------- -------------------------------------------- ------------------------------ ------ ------------------------------
100001002 [{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}] ["1", "2"] "$[1]" "oromp5ZEhkLz1BXELF-cxyqap-uw" 1 oromp5ZEhkLz1BXELF-cxyqap-uw
100001002 [{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}] ["1", "2"] "$[1]" "oromp5ZEhkLz1BXELF-cxyqap-uw" 2 oKTQ41OqjJ848_SPe7wbTZO9RmOE
100001006 [{"app_id": "1", "openid": "oromp5QBh9-a8VQlM99knmq2eM7k"}] ["1"] (NULL) "oromp5QBh9-a8VQlM99knmq2eM7k" 1 oromp5QBh9-a8VQlM99knmq2eM7k
查询指定key
demo 数据包
data_cache
----------
{"courses:220-0": 24, "course_classes:1": 25701,"test":"666"}
如果我们查询test值或者移除非常简单
SELECT data_cache->'$.test',json_remove(data_cache,'$.test') FROM `daily_caches` WHERE id=32
result
data_cache->'$.test' json_remove(data_cache,'$.test')
-------------------- --------------------------------------------------
"666" {"courses:220-0": 24, "course_classes:1": 25701}
但是如果我们查询里面的 course_classes:1 key, 上面的方法就不行了, 需要下面的写法才行, 试了好久才试出来的
SELECT data_cache->'$."course_classes:1"',json_remove(data_cache,'$."course_classes:1"') FROM `daily_caches` WHERE id=32
result
data_cache->'$."course_classes:1"' json_remove(data_cache,'$."course_classes:1"')
---------------------------------- ------------------------------------------------
25701 {"test": "666", "courses:220-0": 24}