From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to return a jsonb list of lists (with integers) |
Date: | 2021-02-17 10:46:06 |
Message-ID: | CAADeyWgQGgKYzp3tO=g_HwnZG4bhvqfxyuHgnyQJrJoRWq2CVA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2021-02-17 10:47:44 | Re: How to return a jsonb list of lists (with integers) |
Previous Message | Alexander Farber | 2021-02-17 10:40:20 | Re: How to return a jsonb list of lists (with integers) |