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

[Alexander Farber]

Good evening,

In 13.2 I have 3 SQL queries, which work well and return integer values.

The values I feed to Google Charts (and currently I switch to Chart.js).

Currently I use the queries by calling 3 different custom stored functions
by my Java servlet.

I would like to convert the functions to 1 function, in SQL or if not
possible, then PL/pgSQL.

The new function should return a JSONB list containing 3 other lists, i.e.
something like:

    [ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ]

I think I should use the aggregate function jsonb_agg().

But I can't figure out how to apply it to the 3 queries below, could you
please help me?

CREATE OR REPLACE FUNCTION words_stat_charts(
                in_uid      integer,
                in_opponent integer
        ) RETURNS jsonb AS
$func$
        -- how to return [ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ] ?

        SELECT
                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);

        SELECT
                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)
        );

        SELECT
                SUM(CASE WHEN LENGTH(word) = 2 THEN 1 ELSE 0 END)::integer,
                SUM(CASE WHEN LENGTH(word) = 3 THEN 1 ELSE 0 END)::integer,
                SUM(CASE WHEN LENGTH(word) = 4 THEN 1 ELSE 0 END)::integer,
                SUM(CASE WHEN LENGTH(word) = 5 THEN 1 ELSE 0 END)::integer,
                SUM(CASE WHEN LENGTH(word) = 6 THEN 1 ELSE 0 END)::integer,
                SUM(CASE WHEN LENGTH(word) = 7 THEN 1 ELSE 0 END)::integer,
                SUM(CASE WHEN LENGTH(word) > 7 THEN 1 ELSE 0 END)::integer
        FROM words_scores WHERE uid = in_uid;

$func$ LANGUAGE sql STABLE;

When I try simply wrapping the jsonb_agg() around the 3 columns in the
first query I get the syntax error:

        SELECT
            JSONB_AGG(
                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);

ERROR:  function jsonb_agg(integer, integer, integer) does not exist
LINE 8:             JSONB_AGG(
                    ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.

Thank you for any hints
Alex