On 10th of March, Andrew Dunstan committed patch:

JSON generation improvements.
 
This adds the following:
 
    json_agg(anyrecord) -> json
    to_json(any) -> json
    hstore_to_json(hstore) -> json (also used as a cast)
    hstore_to_json_loose(hstore) -> json
 
The last provides heuristic treatment of numbers and booleans.
 
Also, in json generation, if any non-builtin type has a cast to json,
that function is used instead of the type's output function.
 
Andrew Dunstan, reviewed by Steve Singer.
 
Catalog version bumped.

As I wrote earlier, since 9.2 we have JSON datatype. Thanks to this commit by Andrew, it will be have more functionality.

Previously we had only 2 functions that dealt with json:

• array_to_json(anyarray)
• row_to_json(record)

Now we get two new core functions, and two new in hstore extension.

Let's check first these two new core functions.

json_agg is an aggregate.

Here, is how it works:

$ WITH z AS (SELECT i, i+2 AS j FROM generate_series(1,5) i) SELECT json_agg(z) FROM z;
     json_agg
------------------
 [{"i":1,"j":3}, +
  {"i":2,"j":4}, +
  {"i":3,"j":5}, +
  {"i":4,"j":6}, +
  {"i":5,"j":7}]
(1 ROW)

Seems to be pretty simple – each record is converted to json hash, and these hashes are stored in single array.

to_json is a big harder to show. It's functionality is that it shows json representation of the value. So usually, you'll get something obvious, and simple:

SELECT to_json( now() );
             to_json
---------------------------------
 "2013-03-11 13:07:47.742534+01"
(1 ROW)

But, if the datatype of argument has existing cast to json, this cast will be used. Currently, as far as I can tell, this works only with hstore – using hstore_to_json() function. Which looks more or less like this:

$ WITH x AS (SELECT hstore('a', 'b') || hstore('c', 'd') AS h) SELECT h, hstore_to_json(h) FROM x;
         h          |    hstore_to_json
--------------------+----------------------
 "a"=>"b", "c"=>"d" | {"a": "b", "c": "d"}
(1 ROW)

Finally, the last new function – hstore_to_json_loose() – this is similar to hstore_to_json(), but it does some heuristics to find out where not to quote values.

For example, let's assume we have following hstore:

$ SELECT h FROM t;
                         h
---------------------------------------------------
 "ok"=>"t", "label"=>"whatever", "price"=>"123.45"
(1 ROW)

When I'll convert it using hstore_to_json(), I will get:

$ SELECT hstore_to_json(h) FROM t;
                   hstore_to_json
-----------------------------------------------------
 {"ok": "t", "label": "whatever", "price": "123.45"}
(1 ROW)

but the loose version, will instead produce:

$ SELECT hstore_to_json_loose(h) FROM t;
                hstore_to_json_loose
----------------------------------------------------
 {"ok": TRUE, "label": "whatever", "price": 123.45}
(1 ROW)

As you can see string ‘t' became boolean true, and string ‘123.45' became numeric value 123.45.

While I greatly appreciate Andrew's work, I can't seem to forget, that while we can build JSON values in Pg now, we can't really query them. That is – there is no way (aside from some crude regexp parsing) to treat json array as SQL array. Or hash as a row/record.

Still – this addition is very cool, and if you'd consider generating JSON within Pg – it will greatly simplify what you have to write.