Skip site navigation (1) Skip section navigation (2)

Re: data to json enhancements

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: data to json enhancements
Date: 2012-09-27 13:52:41
Message-ID: CAHyXU0z7HsDQEBCiR8a1nwahqT7yKgiA6SJed_5g5FaXTnC3WA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, Sep 27, 2012 at 8:22 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Sep 26, 2012 at 1:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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


In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2012-09-27 14:00:44
Subject: Re: [v9.3] Extra Daemons (Re: elegant and effective way for running jobs inside a database)
Previous:From: Robert HaasDate: 2012-09-27 13:29:50
Subject: Re: Modest proposal: run check_keywords.pl on every build

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group