postgresql jsonb 内部字段上的 Postgres GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39261409/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres GROUP BY on jsonb inner field
提问by JGem
I am using Postgresql 9.4 and have a table test, with id::intand content::jsonb, as follows:
我正在使用 Postgresql 9.4 并有一个表test，带有id::int和content::jsonb，如下所示：
id | content
----+-----------------
1 | {"a": {"b": 1}}
2 | {"a": {"b": 1}}
3 | {"a": {"b": 2}}
4 | {"a": {"c": 1}}
How do I GROUP BYon an inner field in the contentcolumn and return each group as an array? Specifically, the results I am looking for are:
如何GROUP BY在content列中的内部字段上并将每个组作为数组返回？具体来说，我正在寻找的结果是：
content
---------------------------------
[{"a": {"b": 1}},{"a": {"b": 1}}]
[{"a": {"b": 2}}]
(2 rows)
Trying:
试：
SELECT json_agg(content) as content FROM test GROUP BY content ->> '{a,b}';
Yields:
产量：
content
----------------------------------------------------------------------
[{"a": {"b": 1}}, {"a": {"b": 1}}, {"a": {"b": 2}}, {"a": {"c": 1}}]
(1 row)
采纳答案by redneb
You have to use the #>>operator instead of ->>when the right operand is a json path. Try this:
当正确的操作数是 json 路径时，您必须使用#>>运算符而不是->>。试试这个：
SELECT json_agg(content) as content FROM test GROUP BY content #>> '{a,b}';
Yields:
产量：
content
------------------------------------
[{"a": {"c": 1}}]
[{"a": {"b": 2}}]
[{"a": {"b": 1}}, {"a": {"b": 1}}]
(3 rows)
回答by user698116
I think json_agg() is not the best choice to use it here, since that is concatenating the content values (the whole json data) into an array for a specific group.
It makes more sense to use something like this (and I added 'count(*)', just to have a more common scenario):
我认为 json_agg() 不是在这里使用它的最佳选择，因为它将内容值（整个 json 数据）连接到特定组的数组中。
使用这样的东西更有意义（我添加了'count（*）'，只是为了有一个更常见的场景）：
SELECT content #>> '{a,b}' as a_b, count(*) as count FROM test GROUP BY content #>> '{a,b}';
