CODEWITHSUNDEEP
postgresqlaggregate-functionspostgresql-json
yozel
yozel

Reputation: 471

Getting jsonb field names from query result

I have two table like this:

create table product (
    id serial primary key,
    name text
);

create table selectedattribute (
    id serial primary key,
    product integer references product,
    attribute text,
    val text
);

and I'm creating a materialized view with this select query

select product.name,
jsonb_build_object(
    'color', COALESCE(jsonb_agg(val) FILTER (WHERE attribute='color'), '[]'),
    'diameter', COALESCE(jsonb_agg(val) FILTER (WHERE attribute='diameter'), '[]')
)
from product
left join selectedattribute on product.id = selectedattribute.product
group by product.id;

the problem with this select query is when I add a new attribute, I have to add it to select query in order to create an up-to-date materialized view.

Is there a way to write an aggregate expression that dynamically gets attributes without all these hard-coded attribute names?

You can try my code in SQL Fiddle: http://sqlfiddle.com/#!17/c4150/4

Upvotes: 0

Views: 195

Answers (1)

user330315
user330315

Reputation:

You need to nest the aggregation. First collect all values for an attribute then aggregate that into a JSON:

select id, name, jsonb_object_agg(attribute, vals)
from (
  select p.id, p.name, a.attribute, jsonb_agg(a.val) vals
  from product p
    left join selectedattribute a on p.id = a.product
  group by p.id, a.attribute
) t
group by id, name;

Updated SQLFiddle: http://sqlfiddle.com/#!17/c4150/5

Upvotes: 1

Related Questions