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}}
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)
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)
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}';
Yields:
content
------------------------------------
[{"a": {"c": 1}}]
[{"a": {"b": 2}}]
[{"a": {"b": 1}}, {"a": {"b": 1}}]
(3 rows)