CODEWITHSUNDEEP
postgresqljsonb
ganaraj
ganaraj

Reputation: 420

Postgresql JSONB Query Aggregation

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.

Upvotes: 0

Views: 349

Answers (2)

ganaraj
ganaraj

Reputation: 420

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

Upvotes: 0

user330315
user330315

Reputation:

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.

Upvotes: 2

Related Questions