Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello, thank you for the helpful replies.

I have decided to go with PL/PgSQL for now and also switched from JSONB list of lists to map of lists.

And the custom stored function below works mostly well, except for a special case -

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;

The function works well:

# 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)

Except when 2 players never played with each other - then I get [ null, null, null ]:

# 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)

Is there maybe a nice trick to completely omit "versus" from the returned JSONB map of lists when its [ null, null, null ]?

Thank you
Alex




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux