Обсуждение: How to return a jsonb list of lists (with integers)

Поиск
Список
Период
Сортировка

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

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

От
"David G. Johnston"
Дата:
On Tue, Feb 16, 2021 at 11:47 AM Alexander Farber <alexander.farber@gmail.com> wrote:
Thank you for any hints


json_build_array(...)

David J.

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

От
Michael Lewis
Дата:
Aggregate functions work on a single column to summarize many rows into fewer rows. You seem to be wanting to combine multiple columns which would be done by concatenation or array[column1,column2] or something like that.

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

От
Alexander Farber
Дата:
On Tue, Feb 16, 2021 at 7:52 PM Michael Lewis <mlewis@entrata.com> wrote:
Aggregate functions work on a single column to summarize many rows into fewer rows. You seem to be wanting to combine multiple columns which would be done by concatenation or array[column1,column2] or something like that.

Ah right, Michael, thanks - that is what I realised just after sending the mail. 

I don't have rows here, but a single row with several columns.

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

От
Alexander Farber
Дата:
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

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

От
"David G. Johnston"
Дата:
On Tuesday, February 16, 2021, Alexander Farber <alexander.farber@gmail.com> wrote:
But is it possible in SQL to combine all 3 queries, so that a JSONB list of lists is returned?
So I have to use PL/PgSQL, correct?

With liberal usage of CTEs and subqueries writing a single SQL query should be doable.

David J.

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

От
Alexander Farber
Дата:
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



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

От
Alexander Farber
Дата:
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)
                );

                IF FOUND THEN
                        out_data := JSONB_INSERT(out_data, '{versus}', _versus);
                END IF;

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

От
Alexander Farber
Дата:
I have ended up with the following (to avoid returning [null, null, null] for players who never played with each other):

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

                IF _versus <> '[null, null, null]'::jsonb THEN
                        out_data := JSONB_INSERT(out_data, '{versus}', _versus);
                END IF;

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

От
Pavel Stehule
Дата:
Hi

st 17. 2. 2021 v 11:40 odesílatel Alexander Farber <alexander.farber@gmail.com> 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;

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

От
Alexander Farber
Дата:
Hi Pavel,

why would SELECT INTO be better here?

Thanks
Alex

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

От
Alexander Farber
Дата:
I have tried switching to SELECT INTO, but IF FOUND is still always true, which gives me [ null, null, null ] for some users:

                SELECT JSONB_BUILD_ARRAY(
                        SUM(CASE WHEN (player1 = in_viewer AND state1 = 'won')  OR (player2 = in_viewer AND state2 = 'won')  THEN 1 ELSE 0 END)::integer,
                        SUM(CASE WHEN (player1 = in_viewer AND state1 = 'lost') OR (player2 = in_viewer AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
                        SUM(CASE WHEN (player1 = in_viewer AND state1 = 'draw') OR (player2 = in_viewer AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
                )
                FROM words_games
                WHERE finished IS NOT NULL
                AND in_viewer IN (player1, player2)
                AND in_uid IN (player1, player2)
                INTO _versus;

                IF FOUND THEN           -- for some reason this is always true
                        out_data := JSONB_INSERT(out_data, '{versus}', _versus);
                END IF;

What works for me is:

                IF _versus <> '[null,null,null]'::jsonb THEN
                        out_data := JSONB_INSERT(out_data, '{versus}', _versus);
                END IF;

Greetings
Alex

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

От
Pavel Stehule
Дата:


st 17. 2. 2021 v 15:34 odesílatel Alexander Farber <alexander.farber@gmail.com> napsal:
Hi Pavel,

why would SELECT INTO be better here?

Minimally it doen't use undocumented feature. And you can be sure, so the query is evaluated really like a query.

The expressions are evaluated differently.

Regards

Pavel


Thanks
Alex

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

От
Pavel Stehule
Дата:


st 17. 2. 2021 v 16:02 odesílatel Alexander Farber <alexander.farber@gmail.com> napsal:
I have tried switching to SELECT INTO, but IF FOUND is still always true, which gives me [ null, null, null ] for some users:

                SELECT JSONB_BUILD_ARRAY(
                        SUM(CASE WHEN (player1 = in_viewer AND state1 = 'won')  OR (player2 = in_viewer AND state2 = 'won')  THEN 1 ELSE 0 END)::integer,
                        SUM(CASE WHEN (player1 = in_viewer AND state1 = 'lost') OR (player2 = in_viewer AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
                        SUM(CASE WHEN (player1 = in_viewer AND state1 = 'draw') OR (player2 = in_viewer AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
                )
                FROM words_games
                WHERE finished IS NOT NULL
                AND in_viewer IN (player1, player2)
                AND in_uid IN (player1, player2)
                INTO _versus;

It should be true always. The aggregate returns always one row

postgres=# SELECT sum(pocet_muzu) FROM obce WHERE false;
┌─────┐
│ sum │
╞═════╡
│   ∅ │
└─────┘
(1 row)
 
Regards

Pavel


                IF FOUND THEN           -- for some reason this is always true
                        out_data := JSONB_INSERT(out_data, '{versus}', _versus);
                END IF;

What works for me is:

                IF _versus <> '[null,null,null]'::jsonb THEN
                        out_data := JSONB_INSERT(out_data, '{versus}', _versus);
                END IF;

Greetings
Alex