JSON manipulation functions
in [PgSql]
|
Prev: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
Next: [HACKERS] fillfactor gets set to zero for toast tables
From: Joseph Adams on 14 May 2010 22:35 On Fri, May 14, 2010 at 11:33 AM, Bruce Momjian <bruce(a)momjian.us> wrote: > Joseph Adams wrote: >> == array/object conversion == >> >> The json_object function converts a tuple to a JSON object. If there >> are duplicate column names, there will be duplicate keys in the >> resulting JSON object. >> >> json_object([content [AS name] [, ...]]) returns json >> >> Likewise, the json_array function converts a tuple to a JSON array. >> Column names are ignored. >> >> json_array([content [AS name] [, ...]]) returns json > > Do you see any problems with the fact that JSON arrays can use mixed > data types, e.g.: > > [ 1, 2, 'hi', false] I suppose the json_object and json_array functions would determine which JSON types to employ by looking at the types of arguments given (TEXT values would become strings, INT/FLOAT/NUMERIC/etc. values would become numbers, TRUE/FALSE would become true/false, NULLS would just be null, and JSON values would just be inserted as themselves). Note that json_array('"Hello"'::TEXT) would yield '["\"Hello\""]'::JSON, while json_array('"Hello"'::JSON) would yield '["Hello"]' . Going the other way around, values pulled out of JSON objects and arrays would just be of type JSON. This (revised) function signature says it all: json_values(JSON) returns JSON[] In short, I don't believe mixed data types in arrays will be a problem. json_to_* and *_to_json functions would be used for individual conversions. On Fri, May 14, 2010 at 1:15 PM, Robert Haas <robertmhaas(a)gmail.com> wrote: >> json_keys gets the keys of a JSON object as a set. >> >> json_keys(json) returns setof text > > I would tend to make this return text[] rather than SETOF text. > >> json_values gets the values of a JSON object or the iems of a JSON >> array as a set. >> >> json_values(json) returns setof json > > Similarly I would make this return json[]. Agreed. For those who want sets, the unnest() function can be used. >> -> retrieves an item of a JSON object by key. >> [snip] >> [] retrieves a value of a JSON array/object by (one-based) index. >> [snip] > > I think some kind of array deference and object deference mechanism is > absolutely, positively 100% required. I don't know whether the > particular syntax you've proposed here is best or whether we should > pick another syntax or just use function notation, but I think we > definitely need *something*. If the dereferencing operations aren't available, one could work around it by using json_keys/json_values. Of course, it would be a really clunky solution, and implementing -> will probably be easy compared to implementing those functions. > I also think we need a function called something like json_length() > which returns the length of a list or the number of keys in an object. Definitely. By the way, I'm considering making it so JSON arrays will be treated like objects when it comes to -> and the json_keys function. Thus, json_keys('[1,4,9,16,25]') would yield '{1,2,3,4,5}', and ('[1,4,9,16,25]'::JSON) -> 3 would yield the third item. This would obviate the need for an array-only subscript function/operator. In general, I prefer zero-based counting, but because PostgreSQL's array indexing is one-based, one-based array keys would be better for the sake of consistency. Note that if there was a function like this in the future: -- Access a JSON object like you would in JavaScript json_path('{"squares": [1,4,9,16,25]}', '.squares[2]') There could be confusion, as JavaScript uses zero-based indexing. -- 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 14 May 2010 23:08 On Fri, May 14, 2010 at 10:35 PM, Joseph Adams <joeyadams3.14159(a)gmail.com> wrote: > By the way, I'm considering making it so JSON arrays will be treated > like objects when it comes to -> and the json_keys function. Thus, > json_keys('[1,4,9,16,25]') would yield '{1,2,3,4,5}', and > ('[1,4,9,16,25]'::JSON) -> 3 would yield the third item. This would > obviate the need for an array-only subscript function/operator. > > In general, I prefer zero-based counting, but because PostgreSQL's > array indexing is one-based, one-based array keys would be better for > the sake of consistency. Note that if there was a function like this > in the future: > > -- Access a JSON object like you would in JavaScript > json_path('{"squares": [1,4,9,16,25]}', '.squares[2]') > > There could be confusion, as JavaScript uses zero-based indexing. I think you should take Tom's suggestion and use functional notation rather than operator notation. And then I think you should use 0-based counting to match JS. But I'm game to be outvoted if others disagree. Basically, I think you're right: it will be confusing to have two different notations, and we're certainly going to want a JS equivalent of XPath at some point. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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: Pavel Stehule on 15 May 2010 00:08 2010/5/14 Tom Lane <tgl(a)sss.pgh.pa.us>: > Robert Haas <robertmhaas(a)gmail.com> writes: >> On Thu, May 13, 2010 at 9:47 PM, Joseph Adams >> <joeyadams3.14159(a)gmail.com> wrote: >>> [] retrieves a value of a JSON array/object by (one-based) index. Â In >>> other words, value[n] is equivalent to selecting the nth row of >>> json_values(value) (provided value is of type JSON). Â Examples: >>> >>> SELECT ('[1,2,3,4]'::JSON)[3] >>> -- Result is '3'::JSON >>> >>> SELECT ('{"a": 1, "b": 2, "c": 3, "d": 4}'::JSON)[3] >>> -- Result is '3'::JSON > >> I think some kind of array deference and object deference mechanism is >> absolutely, positively 100% required. Â I don't know whether the >> particular syntax you've proposed here is best or whether we should >> pick another syntax or just use function notation, but I think we >> definitely need *something*. > > Trying to use array notation on something that isn't a SQL array type > is guaranteed to be a mess. Â I strongly recommend that you not attempt > that. Â Just define a function for it. I agree. Maybe you can implement cast to hstore datatype. Regards Pavel Stehule > > Â Â Â Â Â Â Â Â Â Â Â Â 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 > -- 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 25 May 2010 05:37 I started a wiki article for brainstorming the JSON API: http://wiki.postgresql.org/wiki/JSON_API_Brainstorm . I also made substantial changes to the draft of the API based on discussion here and on the #postgresql IRC channel. Is it alright to use the wiki for brainstorming, or should it stay on the mailing list or go somewhere else? I'll try not to spend too much time quibbling over the specifics as I tend to do. While the brainstorming is going on, I plan to start implementing the datatype by itself so I can establish an initial working codebase. -- 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 25 May 2010 06:57
On Tue, May 25, 2010 at 5:37 AM, Joseph Adams <joeyadams3.14159(a)gmail.com> wrote: > I started a wiki article for brainstorming the JSON API: > http://wiki.postgresql.org/wiki/JSON_API_Brainstorm . I also made > substantial changes to the draft of the API based on discussion here > and on the #postgresql IRC channel. > > Is it alright to use the wiki for brainstorming, or should it stay on > the mailing list or go somewhere else? Well, I think it's fine to use the wiki for brainstorming, but before you change the design you probably need to talk about it here. You can't rely on everyone on -hackers to follow changes on a wiki page somewhere. It looks like the API has been overhauled pretty heavily since the last version we talked about here, and I'm not sure I understand it. > I'll try not to spend too much time quibbling over the specifics as I > tend to do. While the brainstorming is going on, I plan to start > implementing the datatype by itself so I can establish an initial > working codebase. Sounds good. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |