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

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

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

I have tried the following, but IF FOUND is always false for some reason:

                _versus := JSONB_BUILD_ARRAY(

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

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

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

                )

                FROM words_games

                WHERE finished IS NOT NULL

                AND (

                        (player1 = in_uid AND player2 = in_opponent) OR

                        (player2 = in_uid AND player1 = in_opponent)

                );

Don't do this. When you want to use a query, then use SELECT INTO.

Regards

Pavel

                IF FOUND THEN

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

                END IF;