Re: nested hstore patch

On Dec 23, 2013, at 6:28 AM, Robert Haas wrote:

> On Fri, Dec 20, 2013 at 6:16 PM, David E. Wheeler <david(at)justatheory(dot)com> wrote:
>> * New operators:
>> + `hstore -> int`: Get string value at array index (starting at 0)
>> + `hstore ^> text`: Get numeric value for key
>> + `hstore ^> int`: Get numeric value at array index
>> + `hstore ?> text`: Get boolean value for key
>> + `hstore ?> int`: Get boolean value at array index
>> + `hstore #> text[]`: Get string value for key path
>> + `hstore #^> text[]`: Get numeric value for key path
>> + `hstore #?> text[]`: Get boolean value for key path
>> + `hstore %> text`: Get hstore value for key
>> + `hstore %> int`: Get hstore value at array index
>> + `hstore #%> text[]`: Get hstore value for key path
>> + `hstore ? int`: Does hstore contain array index
>> + `hstore #? text[]`: Does hstore contain key path
>> + `hstore - int`: Delete index from left operand
>> + `hstore #- text[]`: Delete key path from left operand
>
> Although in some ways there's a certain elegance to this, it also
> sorta looks like punctuation soup. I can't help wondering whether
> we'd be better off sticking to function names.

The key thing is making it easy for people to easily chain calls to their nested hstore objects, and I think these operators accomplish that.

Some of them are fairly intuitive, and I think as a community if we have a) good docs, b) good blog posts on how to use nested hstore, and c) provides clear instructions @ PG events on how to use it, it would be okay, though some things, i.e. extracting the key by a path, might be better being in a function anyway. However, having it as an operator might encourage more usage, only because people tend to think that "functions will slow my query down."

My only concern is the consistency with the generally accepted standard of JSON and with the upcoming jsonb type. I'm not sure if the jsonb API has been defined yet, but it would be great to keep consistency between nested hstore and jsonb so people don't have to learn two different access systems. Data extraction from JSON is often done by the dot operator in implementations, and depending on the language you are in, there are ways to add / test existence / remove objects from the JSON blob.

Being able to extract objects from nested hstore / JSON using the dot operator would be simple and intuitive and general well-understood, but of course there are challenges with doing that in PG and well, proper SQL.

Jonathan