Postgres GROUP BY on jsonb inner field

Question

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)

Please edit your question and provide the results you are looking for. — Gordon Linoff, Sep 01 '16 at 01:30
@GordonLinoff Thanks for your interest! I have improved the question, my attempts, and added my desired results. — JGem, Sep 01 '16 at 01:45

score 17 · Accepted Answer · edited Sep 01 '16 at 15:12

17

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)

edited Sep 01 '16 at 15:12

JGem

377
1
3
7

answered Sep 01 '16 at 02:50

redneb

21,794
6
42
54

I assume you meant to substitute `#>>` in your answer. `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) ` This is great thanks! I will accept this as the answer – JGem Sep 01 '16 at 14:56
Oops, yes, I fixed it. – redneb Sep 01 '16 at 14:58

score 6 · Answer 2 · answered May 10 '18 at 11:47

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}';

Postgres GROUP BY on jsonb inner field

2 Answers2

Linked