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

Re: data to json enhancements

From: Hannu Krosing <hannu(at)krosing(dot)net>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: data to json enhancements
Date: 2012-09-27 22:58:29
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On 09/27/2012 09:18 PM, Andrew Dunstan wrote:
> On 09/27/2012 10:36 AM, Tom Lane wrote:
>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>> On 09/27/2012 09:22 AM, Robert Haas wrote:
>>>> Maybe I am being too pedantic about this and there is a way to make it
>>>> all work nicely, but it sure feels like using the casting machinery
>>>> here is blending together two different concepts that are only
>>>> sometimes the same.
>>> OK. I think that's a very good point. I guess I was kinda swept away by
>>> this being suggested by a couple of influential people.
>> Well, that doesn't make it wrong, it just means there's more work
>> needed.  I'm not that thrilled with magic assumptions about function
>> names either; schema search path issues, for example, will make that
>> dangerous.  We've gone to considerable lengths to avoid embedding
>> assumptions about operator names, and assumptions about function names
>> aren't any better.
>> There are at least three ways we could use the cast machinery for this:
>> (1) Reject Robert's assumption that we have to support both
>> interpretations for every cast situation.  For instance, it doesn't
>> seem that unreasonable to me to insist that you have to cast to text
>> and then to json if you want the literal-reinterpretation behavior.
Maybe cast not to text but to cstring for getting the text-is-already-json ?

That is, reuse the current type io as "literal" casts.

This way a cast of '{"a": 1}'::json::text will fail, as this json value 
really does not
represent a text/string value.

>> The main problem then is figuring out a convenient way to provide
>> interpretation #2 for text itself.
> The trouble is, ISTM, that both things seem equally intuitive. You 
> could easily argue that x::text::json means take x as text and treat 
> it as json, or that it means take x as text and produce a valid json 
> value from it by escaping and quoting it. It's particularly ambiguous 
> when x is itself already a text value. If we go this way I suspect 
> we'll violate POLA for a good number of users.
It may be easier to sort this out if we think in terms of symmetry and 

let's postulate that mytype::json::mytype and json::mytype::json should 
always reproduce the original result or they should fail.

so '[1,2,3]'::text::json::text === '[1,2,3]'::text with intermediate  
json being '"[1,2,3]"'

and '[1,2,3]'::json::text::json fails the json-->text casts as 
'[1,2,3]'::json does not represent
a text value (in a similar way as '[1,2,3]'::json::date fails)

on the other hand '[1,2,3]'::json::int[]::json should succeed as there 
is a direct mapping to int array.


>> (3) Invent an auxiliary type along the lines of "json_value" and say
>> that you create a cast from foo to json_value when you want one
>> interpretation, or directly to json if you want the other.  Then
>> things like record_to_json would look for the appropriate type of cast.
>> This is a bit ugly because the auxiliary type has no reason to live
>> other than to separate the two kinds of cast, but it avoids creating
>> any new JSON-specific mechanisms in the type system.
As suggested above, this special type could be on the other side - the 
type cstring as
already used for type io functions

the main problem here is, that currently we do interpret ::text::json as it
were the type input function.

we do proper selective quoting when converting to back json

hannu=# create table jt(t text, j json);
hannu=# insert into jt values ('[1,2]','[3,4]');
hannu=# select row_to_json(jt) from jt;
(1 row)

but we do automatic casting through cstring and json type input func 
when converting to json.

hannu=# select t::json, j::json from jt;
    t   |   j
  [1,2] | [3,4]
(1 row)

This should probably be cleaned up.

> I could accept this. The reason is that very few types are in fact 
> going to need a gadget like this. Yes it's mildly ugly, but really 
> fairly unobtrusive.
> cheers
> andrew
>> There might be some other ideas I'm not thinking of.
> Yeah. You've done better than me though :-)
> cheers
> andrew

In response to


pgsql-hackers by date

Next:From: Magnus HaganderDate: 2012-09-27 22:59:24
Subject: Re: Patch: incorrect array offset in backend replication tar header
Previous:From: Tom LaneDate: 2012-09-27 22:55:34
Subject: Re: Patch: incorrect array offset in backend replication tar header

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