Re: How to return a jsonb list of lists (with integers)

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

st 17. 2. 2021 v 16:02 odesílatel Alexander Farber <alexander.farber@xxxxxxxxx> napsal:

I have tried switching to SELECT INTO, but IF FOUND is still always true, which gives me [ null, null, null ] for some users:

                SELECT JSONB_BUILD_ARRAY(

                        SUM(CASE WHEN (player1 = in_viewer AND state1 = 'won')  OR (player2 = in_viewer AND state2 = 'won')  THEN 1 ELSE 0 END)::integer,

                        SUM(CASE WHEN (player1 = in_viewer AND state1 = 'lost') OR (player2 = in_viewer AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,

                        SUM(CASE WHEN (player1 = in_viewer AND state1 = 'draw') OR (player2 = in_viewer AND state2 = 'draw') THEN 1 ELSE 0 END)::integer

                )

                FROM words_games

                WHERE finished IS NOT NULL

                AND in_viewer IN (player1, player2)

                AND in_uid IN (player1, player2)

                INTO _versus;

It should be true always. The aggregate returns always one row

postgres=# SELECT sum(pocet_muzu) FROM obce WHERE false;
┌─────┐
│ sum │
╞═════╡
│   ∅ │
└─────┘
(1 row)

 

Regards

Pavel

                IF FOUND THEN           -- for some reason this is always true

                        out_data := JSONB_INSERT(out_data, '{versus}', _versus);

                END IF;

What works for me is:

                IF _versus <> '[null,null,null]'::jsonb THEN

                        out_data := JSONB_INSERT(out_data, '{versus}', _versus);

                END IF;

Greetings
Alex