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