is JSON really "a type" (Re: data to json enhancements) - Mailing list pgsql-hackers

On 09/26/2012 06:46 PM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> Drawing together various discussions both here and elsewhere (e.g. the
>> PostgresOpen hallway track) I propose to work on the following:
>> 1. make datum_to_json() honor a type's cast to json if it exists. The
>> fallback is to use the type's string representation, as now.
>> 2. add a cast hstore -> json (any others needed for core / contrib types ?)
>> 3. add a to_json(anyelement) function
>> 4. add a new aggregate function json_agg(anyrecord) -> json to simplify
>> and make more effecient turning a resultset into json.
>> Comments welcome.
> ISTM the notion of to_json(anyelement) was already heavily discussed and
> had spec-compliance issues ... in fact, weren't you one of the people
> complaining?  What exactly does #3 mean that is different from the
> previous thread?
>
> Also, on reflection I'm not sure about commandeering cast-to-json for
> this --- aren't we really casting to "json member" or something like
> that?  The distinction between a container and its contents seems
> important here.  With a container type as source, it might be important
> to do something different if we're coercing it to a complete JSON
> value versus something that will be just one member.  I'm handwaving
> here because I don't feel like going back to re-read the RFC, but
> it seems like something that should be considered carefully before
> we lock down an assumption that there can never be a difference.
>
>             regards, tom lane
Reflecting over the dual possible interpretation of what it does mean to 
convert between "text" and "json" data types it has dawned to me that 
the confusion may come mainly from wanting json to be two things at once:

1. - a serialisation of of a subset of javascript objects to a string.

2. - a dynamic type represented by the above serialisation.

case 1
------

If we stick with interpretation 1. then json datatype is really no more 
than a domain based on "text" type and having a CHECK is_valid_json() 
constraint.

For this interpretation it makes complete sense to interpret any text as 
already being serialised and no casts (other than casts to a text type) 
have place here.

a few datatypes - like hstore - could have their "to_json_text()" 
serialiser functions if there is a better serialisation to text than the 
types defaul one, but other than that the "serialise to text and quote 
if not null, boolean or numeric type" should be needed.

if there is strong aversion to relying on function names for getting the 
right serialisation function, we could invent a new "cast-like" feature 
for serialising types so we could define a serialiser for hstore to json 
using

CREATE SERIALISATION (hstore AS json)
WITH FUNCTION hstore_as_json(hstore);

this probably will not be any safer than just using the name for lookup 
directly unless we place some restrictions on who is allowed to create 
the serialisation;

case 2
------

My suggestions on using typecasts for convert-to-json were result of 
this interpretation of json-as-dynamic-type.

Having thought more of this I now think that we probably should leave 
JSON alone and develop an separate dynamic type here.

I have started work on doing this based on ideas from BSON data format, 
except using postgreSQL datatypes.

It will still have to solve similar problems we have had here with JSON, 
but being both a new type and a binary type there will probably be no 
expectation of 1-to-1 conversion from to-text.

Will post here soon for more discussion on what this ned type does and 
how it should be used.

Hannu