From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to return a jsonb list of lists (with integers) |
Date: | 2021-02-16 20:55:28 |
Message-ID: | CAADeyWiYkN5+mLa-GnJ7CoeeGOBf4sX+zWE9Cx2PBsPGM_noYQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-02-16 21:02:04 | Re: How to return a jsonb list of lists (with integers) |
Previous Message | Alexander Farber | 2021-02-16 20:30:59 | Re: How to return a jsonb list of lists (with integers) |