How to return a jsonb list of lists (with integers)
| От | Alexander Farber |
|---|---|
| Тема | How to return a jsonb list of lists (with integers) |
| Дата | |
| Msg-id | CAADeyWjis00Lf4ZGZ=SLBBZNcUxiJHHWU7y3zjqRPO2O9+iWEg@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: How to return a jsonb list of lists (with integers)
("David G. Johnston" <david.g.johnston@gmail.com>)
Re: How to return a jsonb list of lists (with integers) (Michael Lewis <mlewis@entrata.com>) |
| Список | pgsql-general |
Good evening,
When I try simply wrapping the jsonb_agg() around the 3 columns in the first query I get the syntax error:
Thank you for any hints
Alex
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).
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] ]
[ [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.
Alex
В списке pgsql-general по дате отправления: