Re: JSON manipulation functions

On Fri, May 14, 2010 at 11:33 AM, Bruce Momjian <bruce(at)momjian(dot)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(at)gmail(dot)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.