1

I have a SQL API to my Postgres database that returns JSON.

I have two tables: holidays, which has a schema of name, hd_id. And photos, which are photos take on that day. Its schema is url, caption, h_id.

I'd like to create a nested json object like the one down below. The SQL I'm running is

    SELECT holidays.name, holidays.h_id, 
    concat('[', group_concat(concat('{"src":"', photos.url, '","caption":"', photos.caption '"}', separater ','), ']') ) 
    FROM holidays 
    INNER JOIN photos 
    ON holidays.h_id = photos.h_id 
    GROUP BY holidays.h_id

But that gives me the error "schema "photos" does not exist". Photos is a table, not a schema. I don't seem to be making the same mistake as this seemingly related question. I'm not sure how else to structure the JOIN.

Here's the desired JSON output.

   [
    {
        name: 'Labor Day',
        h_id: 'a1',
        photos: [
            {
                src: 'url',
                caption: 'text'
            },
            {
                src: 'url',
                caption: 'text'
            }
        ]
    },
    {
        name: 'MLK Day',
        h_id: 'a2',
        photos: [
            {
                src: 'url',
                caption: 'text'
            },
            {
                src: 'url',
                caption: 'text'
            }
        ]
    }
   ]
Community
  • 1
  • 1
mhkeller
  • 713
  • 1
  • 8
  • 19
  • 1
    It looks like Postgres [does not have group_concat](http://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent). – Avery Richardson Aug 30 '13 at 16:45

1 Answers1

1

There's no group_concat in PostgreSQL. You may use string_agg:

select
    h.name, h.h_id, 
    '[' || string_agg('{"src":"' || p.url || '", "caption":"' || p.caption  || '"}', ',') || ']'
from holidays as h
    inner join photos as p on h.h_id = p.h_id 
group by h.name, h.h_id

see sql fiddle demo with this example

Or use JSON functions. There's also nice JSON support in version 9.3

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • That works thanks! I ended up adding "AS children" after the string_agg line (the third line in your answer code) so that the returned column has a nice name. – mhkeller Sep 04 '13 at 01:09