postgresql 从json数组中提取json值，并分组，汇总

最新推荐文章于 2024-10-03 16:55:46 发布

原创最新推荐文章于 2024-10-03 16:55:46 发布 · 2w 阅读

4 ·

CC 4.0 BY-SA版权

sql 专栏收录该内容

10 篇文章

订阅专栏

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