JSON type in ADQL and TAP

[Markus Demleitner]

Hi Igor,

On Thu, Aug 17, 2023 at 04:22:24PM -0400, Igor Chilingarian wrote:
> I'm cross-posting this to DAL and ADQL.

[I don't think the ADQL list is still read; DAL is the right place
for ADQL discussions, though]

> So, now the question is: how should we handle JSON on the ADQL/TAP level?
> From what I understand, there is no JSON type in ADQL. Neither there is
> support for those weird JSON-specific SQL operators (@> etc.)

> Or should we perhaps include JSON types into ADQL? They're now available in
> many DBMSs (PostgreSQL, Oracle, SQLite, ...) even though only PostgreSQL
> and Oracle knows how to index them.

There are at least two aspects to this:

(1) Returning JSON.  That is, I think, not much of an ADQL problem.
The place to specify that would be DALI.  I think in there we could
just define a json xtype, and json-aware clients could automatically
provide deserialised data.  That proposal isn't new; in
<http://mail.ivoa.net/pipermail/apps/2021-February/001493.html> I've
said a few words about why I don't like it too much.  That has not
changed, but I will certainly not argue against json-valued fields in
VOTable either.

(2) Querying against JSON columns, possibly even json-manipulating
operators.  That's ADQL's turf all right.  And I think the way to
deal with this is to draw up a possible extension just as I've done
it for vectors
<https://wiki.ivoa.net/twiki/bin/view/IVOA/ADQLVectorMath>.  I'd be
happy to do the implementation part once such a draft is there.

We should then see what works, what doesn't, and what's missing, and
distill something from it that *might* make it into ADQL 2.2 (which
is where I hope my vector proposal will end up in).

          -- Markus