Postgresql JSONB Query Aggregation-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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