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)
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
The UNNEST function takes an array within a column of a single row and returns the elements of the array as multiple rows. CAST converts the JSON type to an ARRAY type which UNNEST requires. JSON_EXTRACT uses a jsonPath expression to return the array value of the result key in the data.
PostgreSQL provides two native operators -> and ->> to help you query JSON data. The operator -> returns JSON object field as JSON. The operator ->> returns JSON object field as text.
In the case of jsonb, it is comparatively faster to process the data as no reparsing is needed. JSON does not support indexing, unlike jsonb. Jsonb supports indexing to search for the keys or key/ value pairs which is a great advantage at a bigger database jsonb documents.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With