JSON type in ADQL and TAP

[Gregory MANTELET]

Hi Igor,


On 21/08/2023 09:17, Markus Demleitner 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]


Me too, I am not aware of this mailing list. If it exists, even me, I am 
not part of it.
So, I agree, DAL is the right place for this discussion.


>> 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.


I agree, this is more a DALI/VOTable issue. I can imagine that it is 
just about adding an XType for JSON in DALI.

Now, whether or not we want JSON fields in astronomical tables is 
another discussion.

Personally, I agree with Igor on the fact that JSON data are now 
everywhere, including in databases. So, at some point, we will have to 
deal with such things.

On the other hand, I also agree with Markus: having JSON data 
encapsulated inside tables is kind of nasty in term of user experience. 
Generally, we expect data that we can easily read, but this is not 
immediately possible with JSON data (especially with long and/or complex 
data structure) ; a parsing/query step is needed most of the time if the 
table is exported in something else than a JSON file. A better approach 
could be to instead provide a link (datalink involved somewhere ?) 
toward this JSON document ; but not sure, it is a better user experience 
either.

This topic could probably be discussed in more details during a DAL 
running meeting or during an interop meeting.


> (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).


I completely agree with Markus. And yes, I also agree that vector things 
should be part of ADQL 2.2.

Cheers,
Grégory