Where condition inside json_agg in postgres-CodePudding

I'm trying to return a JSON object from postgres that looks something like this:

[
{id: 1, name: "some organisation name", alias: [{alias:"alt name"}, {alias:"another name"}]}
...]

My query below works fine, except that I want to add a where condition, referencing the org_aliases table

SELECT json_build_object('id', table1.id, 'name', table1.name, 'alias', a.alias) as json
   FROM orgs table1
    CROSS JOIN LATERAL (
      SELECT json_agg(agg) AS alias
      FROM  (
        SELECT table2.alias as name
        FROM org_aliases table2
        WHERE 
            table2.org_id = table1.id
         ) agg
    ) a
    WHERE
        table1.name ilike 'nspcc' 
        or table2.alias ilike 'nspcc';

It fails on the last line (missing from condition on table2). I can see why it doesn't allow me to do this, as I'm referencing something inside a sub query.

My question, is what's the best way to handle this? My only idea is that I need to join the org_aliases again so I can add a where condition. But if anyone has a better idea for how I structure the query to avoid duplication, that would be amazing.

CodePudding user response：

Not sure if it's exactly what you're looking for, but it does give you the required result for your example:

SELECT
  json_build_object('id', orgs.id
                   , 'name', name
                   , 'alias', json_agg(
                     json_build_object('name', alias)
                     )
                   )
FROM orgs
  LEFT JOIN org_aliases ON orgs.id = org_aliases.org_id
WHERE name ILIKE '