Re: STRING_AGG and GROUP BY

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Mon, Mar 19, 2018 at 1:54 PM, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

I've come up with the following query, wonder if you meant something similar -

http://sqlfiddle.com/#!17/4ef8b/48

WITH cte1 AS (

SELECT 

    mid,

    STRING_AGG(x->>'letter', '') AS tiles

FROM (

        SELECT 

            mid,

            JSONB_ARRAY_ELEMENTS(m.tiles) AS x

        FROM moves m

        WHERE m.gid = 1

) AS z

GROUP BY mid),

cte2 AS (

        SELECT 

        mid,

        STRING_AGG(y, ', ') AS words

    FROM (

        SELECT 

            mid,

            FORMAT('%s (%s)', s.word, s.score) AS y

        FROM scores s

        WHERE s.gid = 1

) AS z

GROUP BY mid)

SELECT mid, tiles, words 

FROM cte1 JOIN cte2 using (mid) ORDER BY mid ASC;

​​Yes.  It does end up presuming that the sets moves.mid and scores.mid ​are identical but that is probably a safe assumption.  Repetition of m.gid = 1 is worth avoiding in theory though depending on how its done the solution can be worse than the problem (if the planner ends up unable to push the predicate down).

​David J.