Re: additional json functionality

On 11/13/2013 06:45 AM, Merlin Moncure wrote:> I'm not so sure we should
require hstore to do things like build
> Also, json_object is pretty weird to me, I'm not sure I see the
> advantage of a new serialization format, and I don't agree with the
> statement "but it is the caller's reponsibility to ensure that keys
> are not repeated.".

This is pretty standard in the programming languages I know of which use
JSON.

> I think the caller should have no such
> responsibility. Keys should be able to repeated.

Apparently your experience with using JSON in practice has been fairly
different from mine; the projects I work on, the JSON is being
constantly converted back and forth to hashes and dictionaries, which
means that ordering is not preserved and keys have to be unique (or
become unique within one conversion cycle). I think, based on the
language of the RFC and common practice, that it's completely valid for
us to require unique keys within JSON-manipulation routines.

Certainly the upcoming binary storage is going to require unique keys.
For that matter, both MongoDB and CouchDB store unique, unordered keys.
And ever supporting CRUD functions (i.e. "update this key") is going to
require uniqueness.

> Putting it all together, I'd consider:
> *) dropping json_object (although maybe there is a case I'm not
thinking about)
> *) changing json_build function names to get the json prefix
> *) adding a json object constructor that takes two parallel arrays as
> arguments.

I was with you until the third idea. Huh?

The scripting languages I use (Perl, Python) have functions which
convert a list/array to a hash/dictionary. In each case, the standard
input is a single list/array in the form [ k, v, k, v, k, v ]. Now,
while there are standard language functions which support munging two
parallel arrays into one hash (such as Python's zip()), these are less
frequently used. Supporting the zip() option without supporting the [
k, v ] array option would be a bizarre and puzzling approach to most
programmers I know. I can see three approaches which make sense:

1. we don't include json_object at all.
2. we include the existing json_object
3. we include json_object, plus a second json_object function which
takes two arrays

Keep in mind that all of Andrew's functions came out of real-life use
cases of writing applications which return JSON to the caller, so they
are based on real needs to fill holes in our JSON-building function library.

In the case of json_object, the need was to supply "column" labels
where, usually due to calculated columns, none exist in the input. Take
the example where I want to return a bunch of aggregates from a table as
a series of json objects with user-friendly labels:

SELECT build_json_object( "dept", department, "total_costs", sum(costs),
"running_total", running_sum() )
FROM ....

Where it becomes even more useful is when you want the json label to be
the result of a calculated expression:

SELECT build_json_object ( department, sum() )

Yes, you could do this with a two-array version as well; it's just not
more intuitive, and in cases where you have dozens of columns, puts you
in column-counting hell.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com