Good afternoon, what would be please the best way to generate a list of JSON objects out of an SQL join?
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; which delivers me results like: words=> select words_get_longest2(2); words_get_longest2 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- [{"row":2,"gid":1,"created":"17.03.2017 09:06","finished":null,"player1":2,"player2":1,"score1":24,"score2":51,"female1":0,"female2":0,"given1":"Ghijk4","given2":"Abcde1","photo1":null," photo2":"https://vk.com/images/camera_200.png","place1":null,"place2":null,"word":"СМЯТИЕ","score":16,"tiles":[{"col": 5, "row": 13, "value": 1, "letter": "Е"}, {"col": 5, "row": 12, "val ue": 1, "letter": "И"}, {"col": 5, "row": 11, "value": 2, "letter": "Т"}, {"col": 5, "row": 10, "value": 0, "letter": "Я"}, {"col": 5, "row": 8, "value": 2, "letter": "С"}]},{"row":1,"gid ":1,"created":"17.03.2017 09:06","finished":null,"player1":2,"player2":1,"score1":24,"score2":51,"female1":0,"female2":0,"given1":"Ghijk4","given2":"Abcde1","photo1":null,"photo2":"https: //vk.com/images/camera_200.png","place1":null,"place2":null,"word":"МЕХ","score":8,"tiles":[{"col": 6, "row": 9, "value": 1, "letter": "Е"}, {"col": 5, "row": 9, "value": 2, "letter": "М" }]}] (1 row) Is that please a good approach or is there maybe a better way with PostgreSQL 9.5 or 9.6? Thank you Alex