Postgres Group by on JSONb Inner Field

Postgres GROUP BY on jsonb inner field

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)

GROUP BY in Postgres - no equality for JSON data type?

Shorter, faster and more elegant with a LATERAL join:

SELECT DISTINCT ON (t.team->>'Name') t.team
FROM   matches m, json_array_elements(m.match->'Teams') t(team);
ORDER  BY t.team->>'Name', m.id DESC;  -- to get the "last"

If you just want distinct teams, the ORDER BY can go. Related:

• Query for element of array in JSON column
• Query for array elements inside JSON type

JSON and equality

There is no equality operator for the json data type in Postgres, but there is one for jsonb (Postgres 9.4+):

• How to query a json column for empty objects?

Querying JSONB column in nested structure

You should use CROSS JOIN and jsonb_array_elements functions to extract array for each element of records

Demo

select
  et.id,
  string_agg(ptmp.value -> 'price' ->> 'value', ',')
from 
  example_table et
  cross join jsonb_array_elements(et.result -> 'report' -> 'products') p
  cross join jsonb_array_elements (p.value -> 'types') pt
  cross join jsonb_array_elements (pt.value -> 'metadata' -> 'prices') ptmp
group by et.id

How to group results by values that are inside json array in postgreSQL

You can extract keys and values as arrays first by using jsonb_each, and then unnest the generated arrays by jsonb_array_elements_text. The rest is classical aggregation along with sorting through the count values by window function such as

SELECT key, value
  FROM ( SELECT j.key, jj.value,  
                RANK() OVER (PARTITION BY j.key ORDER BY COUNT(*) DESC)
           FROM t,
        LATERAL jsonb_each(js) AS j,
        LATERAL jsonb_array_elements_text(j.value) AS jj
          GROUP BY j.key, jj.value ) AS q
 WHERE rank = 1   

Demo

How to sum of jsonb inner field in postgres?

You can use json_array_element() to expand the jsonb array, and then aggregate by metal id:

select obj ->> 'metal_id' metal_id, sum((obj ->> 'pc')::int) cnt
from mytable t
cross join lateral jsonb_array_elements(t.js) j(obj)
group by obj ->> 'metal_id'
order by metal_id

Demo on DB Fiddle:

metal_id | cnt
:------- | --:
1        |   5
2        |   1
5        |   4

Complex JSON B aggregation with GROUP BY

You can use the jsonb_object_agg function like this to get the sum of all keys without declaring them:

select id,  jsonb_object_agg(key, sum)  from
(
    select   id, key, sum(value::int)
    from my_table 
    cross join jsonb_each_text(content)
    group by id, key
) tmp_each group by id

Demo in DBfiddle

How to aggregate json fields when using GROUP BY clause in postgres?

You are aggregating on two different levels. I can't think of a solution that wouldn't need two separate aggregation steps. Additionally to aggregate the material information all arrays of the item_id have to be unnested first, before the actual pieces value can be aggregated for each material_id. Then this has to be aggregated back into a JSON array.

with pieces as (

  -- the basic aggregation for the "detail pieces"
  select dt.item_id, sum(dt.pieces) as pieces
  from detail_table dt
  where dt.item_id = 2
  group by dt.item_id

), details as (

  -- normalize the material information and aggregate the pieces per material_id
  select dt.item_id, (m.detail -> 'material_id')::int as material_id, sum((m.detail -> 'pieces')::int) as pieces
  from detail_table dt
    cross join lateral jsonb_array_elements(dt.material_detail) as m(detail)
  where dt.item_id in (select item_id from pieces) --<< don't aggregate too much    
  group by dt.item_id, material_id

), material as (

  -- now de-normalize the material aggregation back into a single JSON array
  -- for each item_id
  select item_id, jsonb_agg(to_jsonb(d) - 'item_id') as material_detail
  from details d
  group by item_id

)
-- join both results together
select p.item_id, p.pieces, m.material_detail
from pieces p
  join material m on m.item_id = p.item_id
; 

Online example

PostgreSQL - Create an array of jsonb using group by clause

Please check the output of this query your expected

-- if use json type
select evaluation_id, nombre, json_agg(formulario)
from your_table
group by 1, 2

-- if use jsonb type
select evaluation_id, nombre, jsonb_agg(formulario)
from your_table
group by 1, 2

---

Related Topics