Proposal: Add JSON support
in [PgSql]
|
From: Robert Haas on 6 Apr 2010 14:10 On Tue, Apr 6, 2010 at 1:31 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> On Tue, Apr 6, 2010 at 12:03 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> 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. > >> With this, I disagree. I see no reason to suppose that a JSON NULL >> and an SQL NULL are the same thing. > > Oh. If they're not the same, then the problem is easily dodged, but > then what *is* a JSON null? I assume we're going to treat JSON much like XML: basically text, but with some validation (and perhaps canonicalization) under the hood. So a JSON null will be "null", just a JSON boolean true value will be "true". It would be pretty weird if storing "true" or "false" or "4" or "[3,1,4,1,5,9]" into a json column and then reading it back returned the input string; but at the same time storing "null" into the column returned a SQL NULL. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 6 Apr 2010 14:20 Robert Haas <robertmhaas(a)gmail.com> writes: > On Tue, Apr 6, 2010 at 1:31 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> Oh. �If they're not the same, then the problem is easily dodged, but >> then what *is* a JSON null? > I assume we're going to treat JSON much like XML: basically text, but > with some validation (and perhaps canonicalization) under the hood. > So a JSON null will be "null", just a JSON boolean true value will be > "true". It would be pretty weird if storing "true" or "false" or "4" > or "[3,1,4,1,5,9]" into a json column and then reading it back > returned the input string; but at the same time storing "null" into > the column returned a SQL NULL. Hmm. So the idea is that all JSON atomic values are considered to be text strings, even when they look like something else (like bools or numbers)? That would simplify matters I guess, but I'm not sure about the usability. In particular I'd want to have something that dequotes the value so that I can get foo not "foo" when converting to SQL text. (I'm assuming that quotes would be there normally, so as not to lose the distinction between 3 and "3" in the JSON representation.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 6 Apr 2010 14:23 On Tue, Apr 6, 2010 at 2:20 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> On Tue, Apr 6, 2010 at 1:31 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> Oh. If they're not the same, then the problem is easily dodged, but >>> then what *is* a JSON null? > >> I assume we're going to treat JSON much like XML: basically text, but >> with some validation (and perhaps canonicalization) under the hood. >> So a JSON null will be "null", just a JSON boolean true value will be >> "true". It would be pretty weird if storing "true" or "false" or "4" >> or "[3,1,4,1,5,9]" into a json column and then reading it back >> returned the input string; but at the same time storing "null" into >> the column returned a SQL NULL. > > Hmm. So the idea is that all JSON atomic values are considered to be > text strings, even when they look like something else (like bools or > numbers)? That would simplify matters I guess, but I'm not sure about > the usability. I'm not sure what the other option is. If you do SELECT col FROM table, I'm not aware that you can return differently-typed values for different rows... > In particular I'd want to have something that dequotes > the value so that I can get foo not "foo" when converting to SQL text. > (I'm assuming that quotes would be there normally, so as not to lose > the distinction between 3 and "3" in the JSON representation.) Yes, that seems like a useful support function. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Yeb Havinga on 6 Apr 2010 15:39 Tom Lane wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: > >> With this, I disagree. I see no reason to suppose that a JSON NULL >> and an SQL NULL are the same thing. >> > > Oh. If they're not the same, then the problem is easily dodged, but > then what *is* a JSON null? > Probably the same as the javascript null. regards, Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Joseph Adams on 6 Apr 2010 16:09
On Tue, Apr 6, 2010 at 12:03 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Petr Jelinek <pjmodos(a)pjmodos.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |