Re: Tackling JsonPath support

On Mon, Nov 28, 2016 at 06:38:55PM -0800, Christian Convey wrote:
> On Mon, Nov 28, 2016 at 6:26 PM, Nico Williams <nico(at)cryptonector(dot)com>
> wrote:
> >
>
> Thanks for the explanation. It sounds like your original point was NOT
> that json-path isn't sufficient for "${specific use X}".

The only uses of SQL w/ JSON I've seen so far in live action are to
implement EAV schemas on PostgreSQL. Since PostgreSQL lacks an ANY
type... using the hstore or jsonb to store data that would otherwise
require an ANY type is the obvious thing to do. Naturally this use
doesn't need deeply nested JSON data structures, so even JSONPath is
overkill for it!

However, there are use cases I can imagine:

- generating complex JSON from complex (e.g., recursive) SQL data where
the desired JSON "schema" is not close to the SQL schema

I've used jq a *lot* to convert schemas. I've also use XSLT for the
same purpose. I've also used SQL RDBMSes and jq together a fair bit,
either having jq consume JSON documents to output INSERT and other
statements, or having a SQL application output JSON that I then
convert to an appropriate schema using jq.

Naturally I can keep using these two tools separately. There's not
much to gain from integrating them for this particular sort of
use-case.

- handling JSON documents with very loose schemata, perhaps arbitrary
JSON documents, embedded in a SQL DB

I've not needed to do this much, so I have no specific examples.
But, of course, one reason I've not needed to do this is that today
it kinda can't be done with enough expressivity.

There are many use-cases for general-purpose programming languages, and
even for very widely-applicable domain-specific programming language.

It's especially difficult to name a specific use-case for a language
that doesn't exist -- in this case that would be SQL + (jq and/or
JSONPath).

> Instead, your point was that jq seems to have many advantages over
> json-path in general, and therefore PG should offer jq instead or, or in
> addition to, json-path.
>
> Is that what you're saying?

Roughly, yes. The distinct advantage is that jq is much more general
and expressive, not unlike SQL itself.

> > Hmm?
>
> Context: The reason I'm trying to work on a json-path implementation is
> that Pavel Stehule suggested it as a good first PG-hacking project for me.
> At the time, it sounded like he had a use for the feature.

I see. I understand that. If you've already made a significant
investment, then I don't blame you for not wanting to risk it. On the
other hand, if melding jsonb and jq happens to be easy, then you'll get
much more bang from it for your investment. Naturally, you do what you
prefer, and if the reality on the ground is JSONPath, then so be it. If
I had time and felt sufficiently strongly, I'd contribute jq
integration; as it is I don't, and beggars can't be choosers.

Nico
--