Question 90135 in PostgreSQL Question posted in
The official documentation can be found here.

Null instead of null values in jsonb_build_object – Postgresql

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

I tried

select(
    models.Animals.id,
    models.Animals.description,
    models.AnimalTypes.name.label('type'),
        func.coalesce(
            func.array_agg(
                func.jsonb_build_object(
                    'id',
                     models.Animals.id
                     )
                ), None
            ).label('animals')
     )
         .join(models.FamilyTypes, models.Family.id == models.Family.family_type_id)
         .outerjoin(models.Animals, models.Animals.campaign_id == models.Animals.id)
         .group_by(models.Family.id, models.FamilyType.name)

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Solved this using case

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

  2. 0

    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}]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search