Thread: [GENERAL] CREATE AGGREGATE on jsonb concat
hi, We do this in our database: CREATE AGGREGATE jsonb_collect(jsonb) ( SFUNC = 'jsonb_concat', STYPE = jsonb, INITCOND = '{}' ); Is there some other built-in aggregate I'm missing that would do the same thing? It just feels like such an obvious feature. Thanks for your advice, Seamus -- Seamus Abshere, SCEA https://github.com/seamusabshere https://linkedin.com/in/seamusabshere https://www.faraday.io
Seamus Abshere <seamus@abshere.net> writes: > We do this in our database: > CREATE AGGREGATE jsonb_collect(jsonb) ( > SFUNC = 'jsonb_concat', > STYPE = jsonb, > INITCOND = '{}' > ); > Is there some other built-in aggregate I'm missing that would do the > same thing? It just feels like such an obvious feature. Doesn't jsonb_agg() do exactly that? regards, tom lane
> Seamus Abshere <seamus@abshere.net> writes: > > We do this in our database: > > > CREATE AGGREGATE jsonb_collect(jsonb) ( > > SFUNC = 'jsonb_concat', > > STYPE = jsonb, > > INITCOND = '{}' > > ); > > > Is there some other built-in aggregate I'm missing that would do the > > same thing? It just feels like such an obvious feature. > On Thu, Jul 6, 2017, at 04:53 PM, Tom Lane wrote: > Doesn't jsonb_agg() do exactly that? hi Tom, That aggregates into an array. Our `jsonb_collect` aggregates into an object. Best, Seamus
Seamus Abshere <seamus@abshere.net> writes: > That aggregates into an array. Our `jsonb_collect` aggregates into an object. Postgres 9.6 has (per https://www.postgresql.org/docs/9.6/static/functions-aggregate.html): * jsonb_agg(expression) * jsonb_object_agg(name, value) In retrospect, I think what I am proposing is: * jsonb_object_agg(expression) Is that sane? Best, Seamus