I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2.
Specifically, given a series of JSON records:
[
{name: "Toby", occupation: "Software Engineer"},
{name: "Zaphod", occupation: "Galactic President"}
]
How would I write the SQL to find a record by name?
In vanilla SQL:
SELECT * from json_data WHERE "name" = "Toby"
The official dev manual is quite sparse:
- http://www.postgresql.org/docs/devel/static/datatype-json.html
- http://www.postgresql.org/docs/devel/static/functions-json.html
Update
I've put together a gist detailing what is currently possible with PostgreSQL https://gist.github.com/2715918 Using some custom functions, it is possible to do things like:
SELECT id, json_string(data,'name') FROM things WHERE json_string(data,'name') LIKE 'G%'; UPDATE II
I've now moved my JSON functions into their own project.
PostSQL - a set of functions for transforming PostgreSQL and PL/v8 into a totally awesome JSON document store