Re: STRING_AGG and GROUP BY

On Mon, Mar 19, 2018 at 1:54 PM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> 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.