Обсуждение: [GENERAL] Generating JSON-encoded list of object out of joined tables

I am using jQuery dataTables plugin and initially was performing an SQL join and then in my PHP script was fetching results row by row and finally encoded them to JSON and feeded to the plugin.

But then I realized that with PostgreSQL that part could be spared and after reading https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql I have rewritten my custom function:

CREATE OR REPLACE FUNCTION words_get_longest2(
                in_uid integer
        ) RETURNS json AS
$func$
        SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(x))) FROM (
                SELECT
                        ROW_NUMBER() OVER () AS row,
                        s.gid AS gid,
                        TO_CHAR(g.created, 'DD.MM.YYYY HH24:MI') AS created,
                        TO_CHAR(g.finished, 'DD.MM.YYYY HH24:MI') AS finished,
                        CASE WHEN g.player1 = in_uid THEN g.player1 ELSE g.player2 END AS player1,
                        CASE WHEN g.player1 = in_uid THEN g.player2 ELSE g.player1 END AS player2,
                        CASE WHEN g.player1 = in_uid THEN g.score1 ELSE g.score2 END AS score1,
                        CASE WHEN g.player1 = in_uid THEN g.score2 ELSE g.score1 END AS score2,
                        s1.female AS female1,
                        s2.female AS female2,
                        s1.given AS given1,
                        s2.given AS given2,
                        s1.photo AS photo1,
                        s2.photo AS photo2,
                        s1.place AS place1,
                        s2.place AS place2,
                        s.word AS word,
                        s.score AS score,
                        m.tiles AS tiles
                FROM    words_scores s
                LEFT JOIN words_games g USING(gid)
                LEFT JOIN words_moves m USING(mid)
                LEFT JOIN words_social s1 ON s1.uid = in_uid
                -- find social record with the most recent timestamp
                AND NOT EXISTS (SELECT 1
                        FROM words_social s
                        WHERE s1.uid = s.uid
                        AND s.stamp > s1.stamp)
                LEFT JOIN words_social s2 ON s2.uid = (CASE WHEN g.player1 = in_uid THEN g.player2 ELSE g.player1 END)
                -- find social record with the most recent timestamp
                AND NOT EXISTS (SELECT 1
                        FROM words_social s
                        WHERE s2.uid = s.uid
                        AND s.stamp > s2.stamp)
                WHERE s.uid = in_uid
                ORDER BY LENGTH(s.word) DESC, s.mid DESC
                LIMIT   10
        ) x;

$func$ LANGUAGE sql STABLE;