STRING_AGG and GROUP BY

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: STRING_AGG and GROUP BY
Date: 2018-03-16 14:17:37
Message-ID: CAADeyWg1oW8PEFHfVos6bDtRituhbfyRw93_XeyOj3U6A0KR0A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good afternoon,

I have prepared an SQL Fiddle for my question:
http://sqlfiddle.com/#!17/4ef8b/2

Here are my 4 test tables:

CREATE TABLE players (
uid SERIAL PRIMARY KEY,
name text NOT NULL
);

CREATE TABLE games (
gid SERIAL PRIMARY KEY,
player1 integer NOT NULL REFERENCES players ON DELETE CASCADE,
player2 integer NOT NULL REFERENCES players ON DELETE CASCADE
);

CREATE TABLE moves (
mid BIGSERIAL PRIMARY KEY,
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
gid integer NOT NULL REFERENCES games ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb NOT NULL
);

CREATE TABLE scores (
mid bigint NOT NULL REFERENCES moves ON DELETE CASCADE,
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
gid integer NOT NULL REFERENCES games ON DELETE CASCADE,
word text NOT NULL CHECK(word ~ '^[A-Z]{2,}$'),
score integer NOT NULL CHECK(score >= 0)
);

Here they are filled with test data (two players Alice and Bob
interchangeably performing moves in game #1):

INSERT INTO players (name) VALUES ('Alice'), ('Bob');
INSERT INTO games (player1, player2) VALUES (1, 2);

INSERT INTO moves (uid, gid, played, tiles) VALUES
(1, 1, now() + interval '1 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "A"}, {"col":
9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value":
2, "letter": "D"}]
'::jsonb),
(2, 1, now() + interval '2 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "X"}, {"col": 8, "row": 12, "value": 10, "letter": "X"}, {"col":
9, "row": 12, "value": 1, "letter": "Z"}]
'::jsonb),
(1, 1, now() + interval '3 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "K"}, {"col": 8, "row": 12, "value": 10, "letter": "K"}, {"col":
9, "row": 12, "value": 1, "letter": "M"}, {"col": 10, "row": 12, "value":
2, "letter": "N"}]
'::jsonb),
(2, 1, now() + interval '4 min', '[]'::jsonb),
(1, 1, now() + interval '5 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "B"}, {"col":
9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value":
2, "letter": "D"}]
'::jsonb),
(2, 1, now() + interval '6 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "P"}, {"col": 8, "row": 12, "value": 10, "letter": "P"}]
'::jsonb);

INSERT INTO scores (mid, uid, gid, word, score) VALUES
(1, 1, 1, 'AACD', 40),
(2, 2, 1, 'XXZ', 30),
(2, 2, 1, 'XAB', 30),
(3, 1, 1, 'KKMN', 40),
(3, 1, 1, 'KYZ', 30),
(5, 1, 1, 'ABCD', 40),
(6, 2, 1, 'PP', 20),
(6, 2, 1, 'PABCD', 50);

For a PHP-script which would display all moves+words+scores played in a
certain game I am trying:

SELECT
mid,
STRING_AGG(x->>'letter', '') AS tiles,
STRING_AGG(DISTINCT y, ', ') AS words
FROM (
SELECT
mid,
JSONB_ARRAY_ELEMENTS(m.tiles) AS x,
FORMAT('%s (%s)', s.word, s.score) AS y
FROM moves m
LEFT JOIN scores s
USING (mid)
WHERE m.gid = 1
) AS z
GROUP BY mid;

Which produces a slightly wrong result (the played letters are duplicated):

mid tiles words
1 AACD AACD (40)
2 XXZXXZ XAB (30), XXZ (30)
3 KKMNKKMN KKMN (40), KYZ (30)
5 ABCD ABCD (40)
6 PPPP PABCD (50), PP (20)

My expected result would actually be:

mid tiles words
1 AACD AACD (40)
2 XXZ XAB (30), XXZ (30)
3 KKMN KKMN (40), KYZ (30)
5 ABCD ABCD (40)
6 PP PABCD (50), PP (20)

Is that actually possible by the means of SQL or should I do it in the PHP
script?

And I can not change the query to: STRING_AGG(DISTINCT x->>'letter', '') AS
tiles,

Because for example in the last move with mid=6 the player Bob had played 2
tiles, both with letter-value "P" and has formed 2 words (PP and PABCD),
but adding distinct would suggest he played a single tile "P".

Thank you
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2018-03-16 14:47:05 Re: SELECT .. FOR UPDATE: find out who locked a row
Previous Message Melvin Davidson 2018-03-16 14:07:40 Re: SELECT .. FOR UPDATE: find out who locked a row