From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | How to return a jsonb list of lists (with integers) |
Date: | 2021-02-16 18:47:37 |
Message-ID: | CAADeyWjis00Lf4ZGZ=SLBBZNcUxiJHHWU7y3zjqRPO2O9+iWEg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dan Nessett | 2021-02-16 18:48:22 | Re: Order by not working |
Previous Message | Michael Lewis | 2021-02-16 18:44:50 | Re: Slow index creation |