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
Best Answer
Postgres 9.2
I quote Andrew Dunstan on the pgsql-hackers list:
Doesn't prevent him from providing an example implementation in PLV8 that should solve your problem. (Link is dead now, see modern PLV8 instead.)
Postgres 9.3
Offers an arsenal of new functions and operators to add "json-processing".
The answer to the original question in Postgres 9.3:
Advanced example:
For bigger tables you may want to add an expression index to increase performance:
Postgres 9.4
Adds
jsonb
(b for "binary", values are stored as native Postgres types) and yet more functionality for both types. In addition to expression indexes mentioned above,jsonb
also supports GIN, btree and hash indexes, GIN being the most potent of these.json
andjsonb
data types and functions.The manual goes as far as suggesting:
Bold emphasis mine.
Performance benefits from general improvements to GIN indexes.
Postgres 9.5
Complete
jsonb
functions and operators. Add more functions to manipulatejsonb
in place and for display.