How do I query using fields inside the new PostgreSQL JSON datatype?

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

3 Answers
3

Leave a Comment