companies, PostgreSQL

Language: Layout:

drop table if exists companies cascade;
drop table if exists company_category_associations cascade;
drop table if exists company_categories cascade;

create table companies (
 id             integer primary key ,
 name           character varying(48) ,
 description    character varying(512),
 tagline        character varying(64) ,
 featured_image integer
) ;

create Table company_category_associations (
 company_id   integer  not null,
 category_id  integer  not null,
 primary key (company_id, category_id)
);

create Table company_categories (
 id           integer primary key, 
 name         character varying(32) not null,
 description  character varying(96)
);

insert into companies  (id, name) values (1, 'Company One'), (2, 'Company Two'), (3, 'Company Three');

insert into company_categories (id, name) values (1, 'Cat 1'), (2, 'Cat 2'), (3, 'Cat 3');
insert into company_category_associations (company_id, category_id) values (1,1), (1,2),(2,1),(2,3),(3,2);

select jsonb_pretty(jsonb_build_object('companies', jsonb_agg(comp_json)))
from (
  select to_jsonb(c) || jsonb_build_object('categories', jsonb_agg(cc)) comp_json
  from companies c
    join company_category_associations cca on cca.company_id = c.id
    join company_categories cc on cc.id = cca.category_id
  group by c.id  
) t;


absolute service time: 0,45 sec