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

以上就是postgresql 从json数组中提取json值，并分组，汇总的全文介绍,希望对您学习和使用数据库有所帮助.

这些内容可能对你也有帮助

更多其它数据库可查看其它数据库列表页。