Re: JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ? - Mailing list pgsql-hackers

On Tue, 2012-05-01 at 09:22 -0700, Andrew Dunstan wrote:
> 
> 
> On Tue, May 1, 2012 at 9:05 AM, Merlin Moncure <mmoncure@gmail.com>
> wrote:
>         On Tue, May 1, 2012 at 10:49 AM, Joey Adams
>         <joeyadams3.14159@gmail.com> wrote:
>         > On Tue, May 1, 2012 at 8:02 AM, Hannu Krosing
>         <hannu@2ndquadrant.com> wrote:
>         >> Hi hackers
>         >>
>         >> After playing around with array_to_json() and row_to_json()
>         functions a
>         >> bit it I have a question - why do we even have 2 variants
>         *_to_json()
>         >
>         > Here's the discussion where that decision was made:
>         >
>         >
>         http://archives.postgresql.org/pgsql-hackers/2012-01/msg01339.php
>         >
>         > To quote:
>         >
>         >>>> why not call all these functions 'to_json' and overload
>         them?
>         >>>
>         >>> I don't honestly feel that advances clarity much. And we
>         might want to overload each at some stage with options that
>         are specific to the datum type. We have various foo_to_xml()
>         functions now.
>         >>
>         >> -1
>         >>
>         >> older proposal is more consistent with xml functions
>         >
>         > The most compelling argument I see here is the one about
>         options
>         > specific to the datum type.
>         
>         
>         I don't find that to be particularly compelling at all.
>          to_timestamp
>         for example supports multiple argument versions depending on
>         the input
>         type.
>         
>         >  * If the JSON type does not yet support, say, converting
>         from a
>         > number, it will be apparent from the names and types of the
>         functions,
>         > rather than being a hidden surprise.  On the other hand,
>         array_to_json
>         > and composite_to_json already convert ANY values to JSON, so
>         this
>         > doesn't matter, anyway.
>         
> 
> 
> I am away from base on a consulting assignment all this week, so my
> connectivity and time are severely limited, and I don't have time to
> respond in depth.
> 
> Let me just point out two things. First, we are approaching a beta
> release. The time for changing this is long since gone, IMNSHO.

First, let me start with stating that I am really happy (and a little
amazed and envious ;) ) with what the current to_json functions are
capable of.

It is already way better than what current query_to_xml could do (at
least int 9.1, may have improved since).


hannu=# select row_to_json(z) from (select 1::int as a, (select s from
(select 2::int as x, 2::text as b)s))z;        row_to_json         
-----------------------------{"a":1,"s":{"x":2,"b":"2"}}
(1 row)

hannu=# select query_to_xml('select 1::int as a, (select s from (select
2::int as x, 2::text as b)s)',true,true,'');                       query_to_xml                         
-------------------------------------------------------------<row
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+                                                          +
<a>1</a>                                                +  <_x003F_column_x003F_>(2,2)</_x003F_column_x003F_>
+</row>                                                    +
+
(1 row)


The reason I am whining now is that with minor adjustments in
implementation it could all be made much more powerful (try cast
to ::json for values before cast to ::text) and much more elegant thanks
to PostgreSQL's built in casting.

If we allowed json to hold any "JSON value" and tried ::json when
generating json for compound types than we would be able to claim that
PostgreSQL supports JSON everywhere, defaulting to representing
officially unsupported types as strings, but allowing users to convert
these to their preferred conventions.

I'd also prefer to have default conversions already included for some of
our sexier types, like intervals (just a two element array) and hstore
(an object) etc.

Suddenly we would be the best match database for Web development and all
things Ajax and also have a widely used built in and adjustable
interchange format to outer world.

> Second, RFC 4627 is absolutely clear: a valid JSON value can only be
> an object or an array, so this thing about converting arbitrary datum
> values to JSON is a fantasy. 

Probably a typo on your part - valid "JSON _text_" is object or array,
valid "JSON value" can also be number, text, true, false and null

What I am arguing for is interpreting our json type as representing a
"JSON value" not "JSON text", this would enable users to adjust and
extend the generation of json values via defining casts for their
specific types - most notably Date* types but also things like hstore,
which has a natural JSON representation as "object" (a list of key:value
pairs for non-js users, a.k.a. a dictionary, hash, etc.)

> If anything, we should adjust the JSON input routines to disallow
> anything else, rather than start to output what is not valid JSON.

I tested python, ruby and javascript in firefox and chrome, all their
JSON generators generate 1 for standalone integer 1 and "a" for
standalone string a , and none refused to convert either to JSON.

-- 
-------
Hannu Krosing
PostgreSQL Scalability and Performance Consultant
2ndQuadrant Nordic
PG Admin Book: http://www.2ndQuadrant.com/books/