Re: JSON manipulation functions

> Well, I think it's fine to use the wiki for brainstorming, but before
> you change the design you probably need to talk about it here.  You
> can't rely on everyone on -hackers to follow changes on a wiki page
> somewhere.  It looks like the API has been overhauled pretty heavily
> since the last version we talked about here, and I'm not sure I
> understand it.

I'll try to explain it in one big nutshell:

Instead of, for instance, json_to_number('5') and number_to_json(5), I
propose changing it to from_json(5)::INT and to_json('5'). Note how
from_json simply returns TEXT containing the underlying value for the
user to cast. I plan to make calling to_json/from_json with arrays or
objects (e.g. to_json(ARRAY[1,2,3]) and from_json('[1,2,3]') ) throw
an error for now, as implementing all the specifics of this could be
quite distracting.

If I'm not mistaken, json_object([content [AS name] [, ...]] | *)
RETURNS json can't be implemented without augmenting the grammar (as
was done with xmlforest), so I considered making it take a RECORD
parameter like the hstore(RECORD) function does, as was suggested on
IRC. However, this may be inadequate for selecting some columns but
not others. Using examples from hstore:

SELECT hstore(foo) FROM foo; => '"e"=>"2.71828", "pi"=>"3.14159"'
-- this works, but what if we only want one field?

SELECT hstore(pi) FROM foo;
-- function type error

SELECT hstore(row(pi)) FROM foo; => '"f1"=>"3.14159"'
-- field name is lost

SELECT hstore(bar) FROM (select pi FROM foo) AS bar; => '"f1"=>"3.14159"'
-- ugly, and field name is *still* lost

To get (and set, which I overlooked before), use json_get and
json_set. These take "JSONPath" expressions, but I don't plan to
implement all sorts of fancy features during the summer. However, I
do plan to support some kind of parameter substitution so you can do
this:

json_get('[0,1,4,9,16,25]', '[%]' %% 2) => '4'::TEXT

For this use case, though, it would be simpler to say:

'[0,1,4,9,16,25]'::JSON -> 2