Postgresql JSONB Query Aggregation

Question

Trying to figure out an aggregation result for a JsonB Column with the following data

part          | data
-----------------------------------
PART1         |[{"type":"box","reference": "box1"},{"type": "dispatch","reference": 
                "d1"},{"type": "dispatch","reference": "d2"}]

Need to write a query that can extract an aggregation result for only type = dispatch The expected result from the query is

part          | data
-----------------------------------
PART1         |d1,d2

Found a few examples across for these executions but none of them apply to a JSONB with an array, most of them are able to work with objects and even when filter is is not needed.

user330315 · Accepted Answer · 2022-03-22 18:21:37Z

2

You can use a JSON path query to return those values as a JSON array:

select part, 
       jsonb_path_query_array(data, '$[*] ? (@.type == "dispatch").reference')
from the_table

Converting that into a comma separated list will be a bit cumbersome though.

answered Mar 22, 2022 at 18:21

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

ganaraj Over a year ago

Thank you, this got me thinking in the right direction. While this works in extracting the result as an array, but converting to a comma separated output was a challenge.

ganaraj · Accepted Answer · 2022-03-23 02:42:24Z

1

Finally Found a Solution for the problem with the following query

select part,(select string_agg(t->>'reference',',') from jsonb_array_elements(data::jsonb) as x(t) where t->>'type' ='dispatch') as output

The following post was of help in arriving at the solution

answered Mar 23, 2022 at 2:42

ganaraj

4203 silver badges14 bronze badges

Collectives™ on Stack Overflow

Postgresql JSONB Query Aggregation

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related