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

CREATE OR REPLACE FUNCTION words_stat_charts(

                in_uid       integer,

                in_opponent  integer, -- optional parameter, can be NULL

                OUT out_data jsonb

        ) RETURNS jsonb AS

$func$

BEGIN

        out_data := JSONB_BUILD_OBJECT();

        -- add a JSON list with 7 integers

        out_data := JSONB_INSERT(out_data, '{length}', JSONB_BUILD_ARRAY(

                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

        ))

        -- add a JSON list with 3 integers

        FROM words_scores WHERE uid = in_uid;

        out_data := JSONB_INSERT(out_data, '{results}', 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);

        -- add a JSON list with 3 integers, but only if in_opponent param is supplied

        IF in_opponent > 0 AND in_opponent <> in_uid THEN

                out_data := JSONB_INSERT(out_data, '{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)

                );

        END IF;

END

$func$ LANGUAGE plpgsql;

# select * from words_stat_charts(5, 6);

                                             out_data

---------------------------------------------------------------------------------------------------

 {"length": [2726, 2825, 2341, 1363, 394, 126, 68], "versus": [6, 3, 0], "results": [298, 151, 0]}

(1 row)

# select * from words_stat_charts(5, 1);

                                                  out_data

------------------------------------------------------------------------------------------------------------

 {"length": [2726, 2825, 2341, 1363, 394, 126, 68], "versus": [null, null, null], "results": [298, 151, 0]}

(1 row)