STRING_AGG and GROUP BY

[Alexander Farber <alexander.farber@xxxxxxxxx>]

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