{"id":2657,"date":"2015-09-22T00:12:18","date_gmt":"2015-09-22T00:12:18","guid":{"rendered":"http:\/\/www.garysieling.com\/blog\/?p=2657"},"modified":"2015-09-22T00:12:18","modified_gmt":"2015-09-22T00:12:18","slug":"flatmap-in-postgres","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/flatmap-in-postgres\/","title":{"rendered":"&#8220;flatMap&#8221; in Postgres"},"content":{"rendered":"<p>Postgres has an aggregation function for combining values into lists, but it seems to lack one for concatenating arrays in a GROUP BY.<\/p>\n<p>Fortunately, this is easy to fix:<\/p>\n<pre lang=\"sql\">\nCREATE AGGREGATE flatMap (anyarray)\n(\n    sfunc = array_cat,\n    stype = ANYARRAY,\n    initcond = '{}'\n);\n<\/pre>\n<p>Example:<\/p>\n<pre lang=\"sql\">\n\nwith testData as (\n  SELECT\n    'group1'     val,\n    ARRAY [1, 2] my_data\n  UNION ALL\n  SELECT\n    'group1'     val,\n    ARRAY [1, 3] my_data\n  UNION ALL\n  SELECT\n    'group2'  val,\n    ARRAY [4] my_data\n)\nselect flatMap(my_data), val\nfrom testData\ngroup by val\n<\/pre>\n<p>Here are the results:<\/p>\n<pre> \n\"{1,2,1,3}\",group1\n{4},group2\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Postgres has an aggregation function for combining values into lists, but it seems to lack one for concatenating arrays in a GROUP BY. Fortunately, this is easy to fix: CREATE AGGREGATE flatMap (anyarray) ( sfunc = array_cat, stype = ANYARRAY, initcond = &#8216;{}&#8217; ); Example: with testData as ( SELECT &#8216;group1&#8217; val, ARRAY [1, 2] &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.garysieling.com\/blog\/flatmap-in-postgres\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;&#8220;flatMap&#8221; in Postgres&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[4],"tags":[160,437,523],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/2657"}],"collection":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/comments?post=2657"}],"version-history":[{"count":0,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/2657\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/media?parent=2657"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/categories?post=2657"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/tags?post=2657"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}