Re: Proposal: Add JSON support

On Tue, Apr 6, 2010 at 12:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Petr Jelinek <pjmodos(at)pjmodos(dot)net> writes:
>> Dne 6.4.2010 7:57, Joseph Adams napsal(a):
>>> To me, the most logical approach is to do the obvious thing: make
>>> JSON's 'null' be SQL's NULL.  For instance, SELECTing on a table with
>>> NULLs in it and converting the result set to JSON would yield a
>>> structure with 'null's in it.  'null'::JSON would yield NULL.  I'm not
>>> sure what startling results would come of this approach, but I'm
>>> guessing this would be most intuitive and useful.
>
>> +1
>
> I think it's a pretty bad idea for 'null'::JSON to yield NULL.  AFAIR
> there is no other standard datatype for which the input converter can
> yield NULL from a non-null input string, and I'm not even sure that the
> InputFunctionCall protocol allows it.  (In fact a quick look indicates
> that it doesn't...)
>
> To me, what this throws into question is not so much whether JSON null
> should equate to SQL NULL (it should), but whether it's sane to accept
> atomic values.  If I understood the beginning of this discussion, that's
> not strictly legal.  I think it would be better for strict input mode
> to reject this, and permissive mode to convert it to a non-atomic value.
> Thus jsonify('null') wouldn't yield NULL but a structure containing a
> null.
>
>                        regards, tom lane
>

Actually, I kind of made a zany mistake here. If 'null'::JSON yielded
NULL, that would mean some type of automatic conversion was going on.
Likewise, '3.14159'::JSON shouldn't magically turn into a FLOAT.

I think the JSON datatype should behave more like TEXT. 'null'::JSON
would yield a JSON fragment containing 'null'. 'null'::JSON::TEXT
would yield the literal text 'null'. However, '3.14159'::JSON::FLOAT
should probably not be allowed as a precaution, as
'"hello"'::JSON::TEXT would yield '"hello"', not 'hello'. In other
words, casting to the target type directly isn't the same as parsing
JSON and extracting a value.

Perhaps there could be conversion functions. E.g.:

json_to_string('"hello"') yields 'hello'
json_to_number('3.14159') yields '3.14159' as text
(it is up to the user to cast it to the number type s/he wants)
json_to_bool('true') yields TRUE
json_to_null('null') yields NULL, json_null('nonsense') fails

string_to_json('hello') yields '"hello"' as JSON
number_to_json(3.14159) yields '3.14159' as JSON
bool_to_json(TRUE) yields 'true' as JSON
null_to_json(NULL) yields 'null' as JSON (kinda useless)

I wonder if these could all be reduced to two generic functions, like
json_to_value and value_to_json.