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

On 09/29/2012 11:47 AM, Hannu Krosing wrote:
> 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.



I am not opposed to making a new type, but I really don't think that 
means we need to do nothing for the existing data type. The suggested 
SERIALIZATION mechanism seems to be fairly intrusive and heavy handed, 
as opposed to the very lightweight mechanism that is Tom's option 3.

Personally I don't have a strong feeling about a general to_json 
function, but it's something other people have asked for. The things I 
do care about are the json_agg function (to which nobody has objected) 
and finding a mechanism for reasonably converting structured types, 
particularly hstore, to json. I still think Tom's suggestion is the best 
and simplest way to do that.

cheers

andrew