Fetching json: best way to do this?

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Fetching json: best way to do this?
Date: 2012-09-23 23:51:41
Message-ID: CACfv+p+jV73wZguyFumsp5BNz9+RH48hZGD7eZYvRouveLVGhA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2012-09-24 01:02:48 Re: Strange dump/restore effect
Previous Message ac@hsk.hk 2012-09-23 23:36:10 Re: Upgrade from 8.4.13 to 9.2.0.1 successful but it still displays 8.4.13 if SELECT version();