Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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:

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

share|improve this question

1 Answer

up vote 21 down vote accepted

I quote Andrew Dunstan on the pgsql-hackers list:

At some stage there will possibly be some json-processing (as opposed to json-producing) functions, but not in 9.2.

Doesn't prevent him from providing an example implementation in PLV8 that should solve your problem.

share|improve this answer
1  
Thanks, I've run into type issues really fast using the PLV8 approach. Looks promising, but not really usable at the moment. – Toby Hede May 13 '12 at 12:23
@TobyHede: Guess we'll have to wait for 9.3 then. – Erwin Brandstetter May 14 '12 at 9:37
2  
I've put some details together gist.github.com/2715918 – Toby Hede May 18 '12 at 5:33
@TobyHede: Excellent work. – Erwin Brandstetter May 21 '12 at 13:49
6  
see also: github.com/tobyhede/postsql – Toby Hede May 25 '12 at 0:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.