Null instead of null values in jsonb_build

I am trying to build query that gets data from 3 different tables using sqlalchemy

I tried

select(
    models.Campaigns.id,
    models.Campaigns.description,
    models.CampaignTypes.name.label('type'),
        func.coalesce(
            func.array_agg(
                func.jsonb_build_object(
                    'id',
                     models.Rewards.id
                     )
                ), None
            ).label('rewards')
     )
         .join(models.CampaignTypes, models.CampaignTypes.id == models.Campaigns.campaign_type_id)
         .outerjoin(models.Rewards, models.Rewards.campaign_id == models.Campaigns.id)
         .group_by(models.Campaigns.id, models.CampaignTypes.name)

i expect to get rewards:null instead of rewards:{"id": null}

CodePudding user response：

Not an answer, just a longer comment. Using psql on a local test table. Using jsonb_agg from Aggregate functions:

 select jsonb_agg(animals.*) from animals;
test(5432)=# select * from animals;
 pk_animals | cond  | animal 
------------ ------- --------
          2 | good  | eagle
          3 | good  | mole
         16 | fair  | heron
         33 | fair  | mole
        200 | good  | crow
         35 | good  | emu
        101 | good  | gopher
         45 | poor  | dog
         50 | great | deer

\pset format wrapped
select jsonb_agg(animals.*) from animals;
                                                                                     jsonb_agg                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"cond": "good", "animal": "eagle", "pk_animals": 2}, {"cond": "good", "animal": "mole", "pk_animals": 3}, {"cond": "fair", "animal": "heron", "pk_animals": 16}, {"cond": "fair.
.", "animal": "mole", "pk_animals": 33}, {"cond": "good", "animal": "crow", "pk_animals": 200}, {"cond": "good", "animal": "emu", "pk_animals": 35}, {"cond": "good", "animal": "g.
.opher", "pk_animals": 101}, {"cond": "poor", "animal": "dog", "pk_animals": 45}, {"cond": "great", "animal": "deer", "pk_animals": 50}]

CodePudding user response：

Solved this using case

    case(
        (func.count(models.Rewards.id) == 0, None),
        else_=(
            func.array_agg(
                func.jsonb_build_object(
                    'id',
                     models.Rewards.id,
                     ...
                     )
                )
             ),
    ).label('rewards')