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

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

Responses

Browse pgsql-general by date

  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