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
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(); |