Re: How to return a jsonb list of lists (with integers)
st 17. 2. 2021 v 16:02 odesílatel Alexander Farber <
alexander.far...@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
>
>
Re: How to return a jsonb list of lists (with integers)
st 17. 2. 2021 v 15:34 odesílatel Alexander Farber < alexander.far...@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)
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)
Hi Pavel, why would SELECT INTO be better here? Thanks Alex
Re: How to return a jsonb list of lists (with integers)
Hi
st 17. 2. 2021 v 11:40 odesílatel Alexander Farber <
alexander.far...@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)
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)
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)
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)
On Tuesday, February 16, 2021, Alexander Farber 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)
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)
On Tue, Feb 16, 2021 at 7:52 PM Michael Lewis 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)
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)
On Tue, Feb 16, 2021 at 11:47 AM Alexander Farber < alexander.far...@gmail.com> wrote: > Thank you for any hints > > json_build_array(...) David J.
How to return a jsonb list of lists (with integers)
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
