Re: json accessors

On Wed, Dec 5, 2012 at 11:14 AM, David E. Wheeler <david(at)justatheory(dot)com> wrote:
> On Nov 28, 2012, at 4:10 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
>>> Yes, it's iterative. And for deeply nested json it might be somewhat
>>> inefficient, although the parser is pretty fast AFAICT. But it's a start.
>>
>> not completely buying that: see comments below. not supporting xpath
>> style decompositions seems wrong to me. IOW, json_get should be set
>> returning (perhaps via wild cards in the keytext) or we need
>> json_each.
>
> The problem I see with the current proposal is that this limitation, it seems to me, would prevent the ability to index nested keys. If you're essentially composing and decomposing JSON values as you drill down, the intermediate JSON values between the original one and the final return value can't be indexed, can they?
>
> For sufficiently large columns, I expect I would want a GIN index to speed JSON value extraction queries. Possible with this proposal?

I think best practices for JSON manipulation (at least in performance
sensitive cases with large documents) are going to be to fully
decompose into sql structures and manipulate after the fact. JSON's
primary role is to serve as data exchange and Andrew's API (with the
tweaks he came up with) seems to facilitate that pretty well; full
decomposition is a snap.

Indexing large documents for fancy querying is a niche case but also
quite complex. This isn't very well covered by xmlpath either btw --
I think for inspiration we should be looking at hstore.

That said, how would you do that? The first thing that jumps into my
mind is to cut right to the chase: Maybe the semantics could be
defined so that implement hackstack @> needle would reasonable cover
most cases.

So my takeaways are:
*) decomposition != precise searching. andrew's api handles the
former and stands on it's own merits.

*) xmlpath/jsonpath do searching (and decomposition) but are very
clunky from sql perspective and probably absolutely nogo in terms if
GIST/GIN. postgres spiritually wants to do things via operators and
we should (if possible) at least consider that first

merlin