postgresql 从json数组中提取json值,并分组,汇总
json数据
{"os": "Android", "chn": "-1", "dan": 0, "sex": 0, "file": "lv_statistics", "time": "2017-01-23 16:47:54", "honor": 0, "chn_id": "-1", "is_pay": 0, "account": "-1", "role_id": -1, "battle_id": 1050, "game_time": 301, "role_name": "-1", "battle_name": "-1", "battle_type": 4, "lv_num_json": [{"lv": 8,"num": 1},{"lv": 9,"num": 10}]"}
SELECT cont->>'lv_num_json' FROM log_info WHERE file = 'lv_statisticsx'
查询结果:[{"lv": 8, "num": 1}, {"lv": 9, "num": 10}]
--例子
select json_array_elements(lv_num_json)->'lv' lv,json_array_elements(lv_num_json)->'num' num from (
select '[{"lv": 8, "num": 1}, {"lv": 9, "num": 10}]'::json lv_num_json
)as t1
输出结果:
lv num
---------------------
8 1
9 10
-- json_array_elements :展开一个JSON数组的JSON值
SELECT json_array_elements((cont->>'lv_num_json')::json)->'lv' lv,json_array_elements((cont->>'lv_num_json')::json)->'num' num
FROM log_info WHERE file = 'lv_statisticsx'
-- 按等级分组,求总人数
select lv ,sum(num::int) from(
SELECT json_array_elements((cont->>'lv_num_json')::json)->>'lv' lv,json_array_elements((cont->>'lv_num_json')::json)->>'num' num
FROM log_info WHERE file = 'lv_statisticsx'
) t1 GROUP BY lv
输出结果:
lv num
---------------------
8 2
9 20
https://www.postgresql.org/docs/9.5/static/functions-json.html
发生变化,如改成这样的json,其中数组json对象作为条件查询:
SELECT (cont ->> 'lv_num_json') :: json FROM log_info
-- output
json
-----------------------------
[{"lv": 1, "uuid": 134217754, "battle_career": 2}, {"lv": 2, "uuid": 134217754, "battle_career": 2}]
[{"lv": 1, "uuid": 134217754, "battle_career": 2}, {"lv": 2, "uuid": 134217754, "battle_career": 2}]
-- "lv_num_json":[{"uuid":134217754,"battle_career":2,"lv":1}]" 按职业查询条件,
SELECT
lv,
count (*) nums
FROM
(
SELECT
json_array_elements (
(cont ->> 'lv_num_json') :: json
) ->> 'lv' lv,
json_array_elements (
(cont ->> 'lv_num_json') :: json
) ->> 'battle_career' battle_career
FROM
log_info
WHERE
cont ->> 'file' = 'lv_statistics'
AND cont ->> 'battle_type' = '2'
AND cont ->> 'time' >= '2017-01-30'
AND cont ->> 'time' <= '2017-02-03 23:59:59'
AND cont ->> 'lv_num_json' LIKE '[%'
) t1
where t1.battle_career in('2','1')
GROUP BY
lv
ORDER BY
lv