Fetching json: best way to do this?

[Joe Van Dyk <joe@xxxxxxxxx>]

Say I want output similar to this:
{
   "id":73,
   "name":"LolShirt 1",
   "uuid":"afe3526818",
   "thumbnails":[
      {
         "filename":"file.png",
         "width":200,
         "height":199,
         "id":79
      },
      {
         "filename":"file.png",
         "width":200,
         "height":199,
         "id":79
      }
   ],
   "channels":[
      {
         "id":8,
         "name":"Animals",
         "slug":"animals"
      },
      {
         "id":12,
         "name":"Hidden",
         "slug":"hidden"
      }
   ]
}


Is this the best way to get that?

create type image_listing as   (filename text, width int, height int,
id int);
create type channel_listing as (id integer, name text, slug text);
create type product_listing as (
  id integer,
  name text,
  uuid text,
  thumbnails image_listing[],
  channels   channel_listing[]);

create function product_listing_json(product_id integer) returns json
language sql stable as $$
  select row_to_json(
    row(
      products.id,
      products.name,
      products.uuid,
      array_agg((m.filename, m.width, m.height, m.id)::image_listing),
      array_agg((c.id, c.title, c.slug)::channel_listing)
     )::product_listing
  )
  from products
  join product_medias m on m.media_of_id = products.id
  left join channels_products cp on cp.product_id = products.id
  join channels c on c.id = cp.channel_id
  where products.id = $1
  group by products.id
$$;


select product_listing_json(id) from products order by id desc;


(https://gist.github.com/377345 contains the above code and expected output)

I'm really looking forward to being able to slurp up a complex json
object in a single sql query, so I'm exploring ways to do that.

Thanks,
Joe


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general