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
I’ve put together a gist detailing what is currently possible with PostgreSQL 9.2.
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