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

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Thank you, David, with json_build_array() it works for a single query -

        SELECT
            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 in_uid IN (player1, player2);

But is it possible in SQL to combine all 3 queries, so that a JSONB list of lists is returned?

I cannot use a UNION, because the first two queries return 3 columns, but the last query returns 7 columns.

So I have to use PL/PgSQL, correct?

Best regards

Alex