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

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.

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.

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.

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

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.

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

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;

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;

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

Hi Pavel,

why would SELECT INTO be better here?

Thanks
Alex

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

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

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