title: DISTINCT ON in an aggregate function in postgres
body: <p>For my problem, we have a schema whereby one photo has many tags and also many comments. So if I have a query where I want all the comments and tags, it will multiply the rows together. So if one photo has 2 tags and 13 comments, I get 26 rows for that one photo:</p>
  
  <pre><code>SELECT
          tag.name, 
          comment.comment_id
  FROM
          photo
          LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
          LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
          LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/9xopi.png" alt="enter image description here"></p>
  
  <p>That's fine for most things, but it means that if I <code>GROUP BY</code> and then <code>json_agg(tag.*)</code>, I get 13 copies of the first tag, and 13 copies of the second tag.</p>
  
  <pre><code>SELECT json_agg(tag.name) as tags
  FROM
          photo
          LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
          LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
          LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
  GROUP BY photo.photo_id
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/jQluN.png" alt="enter image description here"></p>
  
  <p>Instead I want an array that is only 'suburban' and 'city', like this:</p>
  
  <pre><code> [
        {"tag_id":1,"name":"suburban"}, 
        {"tag_id":2,"name":"city"}
   ]
  </code></pre>
  
  <p>I could <code>json_agg(DISTINCT tag.name)</code>, but this will only make an array of tag names, when I want the entire row as json. I would like to <code>json_agg(DISTINCT ON(tag.name) tag.*)</code>, but that's not valid SQL apparently.</p>
  
  <p><strong>How then can I simulate <code>DISTINCT ON</code> inside an aggregate function in Postgres?</strong></p>