'How can I select `array_agg` in `typeorm` with `postgresql`?

I am using typeorm in typescript project to connect to postresql 11. I have below query in sql which gives me expected results:

select "customerUuid", array_agg("siteUuid") from "SiteCustomer" where "customerUuid" in ('id1', 'id2') group by "customerUuid";

but when I use typeorm, it always returns empty array:

     queryRunner.manager.getRepository(SiteCustomer).
          .createQueryBuilder()
          .select('"customerUuid", array_agg("siteUuid")')
          .where('"customerUuid" IN (:customerUuids)', { customerUuids: customerUuids})
          .groupBy('"customerUuid"')
          .getMany();

how can I make typeorm work with array_agg in postgresql?

node.jspostgresqltypeorm


Solution 1:[1]

You can do something like this -

      let baseQuery = queryRunner.manager.getRepository(SiteCustomer)
      .createQueryBuilder()
      .select([
            '"customerUuid" as "customerUuid"',
            // It's better to specify an alias explicitly, because the default one 
            // is implicit and may be unexpected
            'array_agg("siteUuid") as "aggregatedSiteUuids"', 
      ])
      .where('"customerUuid" IN (:customerUuids)', { customerUuids: customerUuids})
      .groupBy('"customerUuid"')

But use -

baseQuery.getRawMany()

in case you are not selecting entities.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 yatish h r

Related Questions