node postgres and getting joined fields with repeated names

Question

I'm writing a node.js app that uses the pg package for accessing a PostgreSQL database. The issue I'm running into is that if I do a query like this:

select * from posts p inner join blogs b on b.id = p.blog_id

When I get the results, they're all in the same namespace, so any field repeated in the blogs table will overwrite those in the posts table.

My question is, what's the best way of binding these results to objects?

Ideally, I'd like a result like:

{
   id: 1,
   name: 'A post name',
   published_at: (some date object),
   blog_id: 1,
   b: {
       id: 1,
       name: 'A blog name'
   }
}

But I'm open to any convenient solution short of adding an alias for every column manually.

Answer 1

http://www.postgresql.org/docs/9.3/static/functions-json.html http://www.postgresql.org/docs/9.4/static/functions-aggregate.html

You may want to look at the json features of Postgres. If I'm understanding you right, and without a test database something like this may be close to what you're looking for:

SELECT 
    p.*, /* Select all the post fields */
    row_to_json(blogs.*) as b /* Use the row_to_json function on the blogs results */
FROM 
    posts p 
INNER JOIN 
    blogs ON (blogs.id=p.blog_id); /* Join blogs on the proper fields */

Returns:

{
    id: 3,
    name: 'test',
    published_at: 2015-10-08,
    blog_id: 2,
    b: {
        id:2,
        name:"test 2"
    }
}

Here's a great tutorial on them: http://bender.io/2013/09/22/returning-hierarchical-data-in-a-single-sql-query/