JSON type in ADQL and TAP

[Igor Chilingarian]

Hi all,

I'm cross-posting this to DAL and ADQL.
Here is the thing: we have a database for our RCSEDv2 project for which we
are building a VO layer, and provided our quite complex data structure
dealing with heterogeneous data collections, we decided to use JSON in
PostgreSQL tables (jsonb native type). The VO layer is constructed using
DaCHS: we are planning to have TAP/ADQL to access the table structure +
SSAP/Datalink + ObsCore.

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

I can see several solutions:
-- Wrap JSON queries into server-side functions/procedures and make them
available -- this will require some work on my end but at least the
functions will be available using standard ADQL queries
-- Create (MATERIALIZED) VIEWs serializing JSON into a set of columns --
but the whole idea of using JSON in RCSEDv2 was to simplify the table
structure and make more generic and simple queries
-- Do nothing and just serve JSON as text but then a lot of functionality
will be lost

Any suggestions from anyone on which path we should follow?
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.

With best regards,
Igor
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ivoa.net/pipermail/dal/attachments/20230817/0ea21ab2/attachment.htm>