On Mon, Mar 19, 2018 at 1:54 PM, Alexander Farber < alexander.far...@gmail.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.
