Postgres 9.4 JSON response

FRiCKLE / ngx_postgres

upstream module that allows nginx to communicate directly with PostgreSQL database.

http://labs.frickle.com/nginx_ngx_postgres/

BSD 2-Clause "Simplified" License

545 stars 122 forks source link

Open dukedougal opened 8 years ago

dukedougal commented 8 years ago

Hello

I am using Postgres 9.4 with JSONB fields.

I want to do a query which returns JSON to the web browser from Nginx.

How do I do this? Currently I get a result set back that contains the field name and then JSON is provided as text.

thanks!

agentzh commented 8 years ago

@dukedougal Hmm, maybe we could let ngx_rds_json module support such JSONB fields directly.

dukedougal commented 8 years ago

After I posted the issue here I tried the following query which pretty much returns the JSON that I need.

There might be improvements or better ways to do it but at the moment I think this is probably good enough.

select row_to_json(row) from (select key, snapk->'name' as name, snapk->>'keywords' as keywords from snapk LIMIT 50 $offset_string) row;

majj commented 8 years ago