Re: JSON manipulation functions
От | Joseph Adams |
---|---|
Тема | Re: JSON manipulation functions |
Дата | |
Msg-id | AANLkTim_fGWOdsj89rvxH3eA7extiK_neNQvJfDDMUbR@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: JSON manipulation functions (Bruce Momjian <bruce@momjian.us>) |
Ответы |
Re: JSON manipulation functions
(Robert Haas <robertmhaas@gmail.com>)
|
Список | pgsql-hackers |
On Fri, May 14, 2010 at 11:33 AM, Bruce Momjian <bruce@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@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.
В списке pgsql-hackers по дате отправления: