Postgres GROUP BY on jsonb inner field
29,819
Solution 1
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)
Solution 2
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):
SELECT content #>> '{a,b}' as a_b, count(*) as count FROM test GROUP BY content #>> '{a,b}';
Author by
JGem
Updated on May 11, 2020Comments
-
JGem almost 4 years
I am using Postgresql 9.4 and have a table
test
, withid::int
andcontent::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 thecontent
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)