jsonb内部字段上的Postgres GROUP BY [英] Postgres GROUP BY on jsonb inner field

问题描述

我使用的是Postgresql 9.4，并有一个表test，其中包含id::int和content::jsonb，如下所示:

I am using Postgresql 9.4 and have a table test, with id::int and content::jsonb, as follows:

 id |     content
----+-----------------
  1 | {"a": {"b": 1}}
  2 | {"a": {"b": 1}}
  3 | {"a": {"b": 2}}
  4 | {"a": {"c": 1}}

如何在content列的内部字段上GROUP BY并将每个组作为数组返回?具体来说，我正在寻找的结果是:

How do I GROUP BY on an inner field in the content column and return each group as an array? Specifically, the results I am looking for are:

             content
---------------------------------
[{"a": {"b": 1}},{"a": {"b": 1}}]
[{"a": {"b": 2}}]
(2 rows)

尝试:

SELECT json_agg(content) as content FROM test GROUP BY content ->> '{a,b}';

收益:

                               content
----------------------------------------------------------------------
[{"a": {"b": 1}}, {"a": {"b": 1}}, {"a": {"b": 2}}, {"a": {"c": 1}}]
(1 row)

推荐答案

当正确的操作数是json路径时，必须使用#>>运算符而不是->>.试试这个:

You have to use the #>> operator instead of ->> when the right operand is a json path. Try this:

SELECT json_agg(content) as content FROM test GROUP BY content #>> '{a,b}';

收益:

              content
------------------------------------
 [{"a": {"c": 1}}]
 [{"a": {"b": 2}}]
 [{"a": {"b": 1}}, {"a": {"b": 1}}]
(3 rows)

这篇关于jsonb内部字段上的Postgres GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！