Re: data to json enhancements - Mailing list pgsql-hackers

On Thu, Sep 27, 2012 at 8:22 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Sep 26, 2012 at 1:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
>
> I feel like there are two different behaviors that someone might want
> here, and a cast cannot mean both.
>
> 1. Please reinterpret the existing value that I have already got as a
> JSON object.  For example, you might have a text field in which you
> have been storing JSON values.  Once you upgrade to 9.2, you might
> want to reinterpret the existing contents of the field - which are
> already valid JSON - as JSON objects.
>
> 2. Please convert the value that I have into a JSON object according
> to a type-specific rule.  For example, you might have a text field in
> which you store arbitrary strings.  But perhaps you need to store
> structured data there, so once you upgrade to 9.2 you might want to
> wrap up your strings inside JSON strings.
>
> Now there is some subtle ambiguity here because in some cases the
> behavior can be exactly the same in both cases.  For example most
> numeric values will get the same treatment either way, but NaN cannot.
>  If you do mynumeric::json, interpretation #1 will fail for NaN but
> interpretation #2 will probably produce something like "NaN".
> Similarly if the type is boolean, we could likely get away with
> producing true and false for either interpretation.  If the type is
> hstore, then #1 is going to fail, but #2 is going to convert "1"=>"2"
> to {"1":"2"}.  So in general it might seem that #2 is the better
> interpretation, because it gives many casts a sensible interpretation
> that is otherwise lacking.
>
> But, what about text?  It seems to me that users will count on the
> fact that '[1,2,3]'::text::json is going to produce [1,2,3] (a JSON
> array containing the first three numbers) and NOT "[1,2,3]" (a JSON
> string containing 7 characters).  And that is emphatically
> interpretation #1.

Hm.  Well, that's a really good point although I kinda disagree with
your assumption: I think it's much cleaner to have:
select  '[1,2,3]'::int[]::json
produce a json array.

All types but text (record[] etc) would seem to use the type structure
to define how the json gets laid out.  'text::json' is an exception,
because there is an implied parse, which I'm starting to unfortunately
think is the wrong behavior if you want to be able to make json datums
out of sql datums: how do you create a vanilla json text datum?

merlin