Postgres JSON密钥计数 [英] Postgres json key count
本文介绍了Postgres JSON密钥计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
给出N条包含json列的记录
Given N records that contain a json column
|ID |Name |JSON
|01 |TEST1 |{"key1" : "value1", "key2": "value2", "key4": "value4"}
|02 |TEST1 |{"key1" : "value1"}
|03 |TEST2 |{"key1" : "value1", "key2": "value2", "key3":"value3"}
...
为一组键计算每个json值的出现的最佳策略是什么,因此对于上面的示例,我将限制为key1,key2,key3并得到:-
What would be the best strategy to count the occurrences of each json value for a set of keys, such that for the example above I would restrict to key1, key2, key3 and get:-
|value1|value2|value3|
|3 |2 |1 |
值将改变,所以我真的不想明确地寻找它们.
The values will change so I don't really want to look for them explicitly.
推荐答案
CREATE TABLE test (id INT4 PRIMARY KEY, some_name TEXT, j json);
copy test FROM stdin;
01 TEST1 {"key1" : "value1", "key2": "value2", "key4": "value4"}
02 TEST1 {"key1" : "value1"}
03 TEST2 {"key1" : "value1", "key2": "value2", "key3":"value3"}
\.
with unpacked as (
SELECT (json_each_text(j)).* FROM test
)
SELECT value, count(*) FROM unpacked WHERE key in ('key1', 'key2', 'key3') group by value;
返回:
value | count
--------+-------
value1 | 3
value3 | 1
value2 | 2
(3 rows)
像显示的那样返回它并不是一个好主意(如果存在40亿个不同的值,您想做什么?),但是您始终可以在应用程序中进行透视,或修改查询以进行透视
Returning it like you showed doesn't strike me as great idea (what would you want to do if there are 4 billion different values?), but you can always pivot in your app, or modify the query to do the pivoting.
这篇关于Postgres JSON密钥计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文