Provide a jsonSchema when adding a mongodb catalog so schema won't be inferred incorrectly

[RoseGoldIsntGay]

Continuing the discussion on slack:

As of today Trino infers the schema for a MongoDB collection using it's first document, as seen here:
https://github.com/trinodb/trino/blob/master/plugin/trino-mongodb/src/main/java/io/trino/plugin/mongodb/MongoSession.java#L789

This causes an issue when multiple documents do not contain the same schema, as you will not be able to query columns that exist in another document. It's possible to solve this issue by manually updating the _schema collection to match all documents in the collection, however this process involves using a non-native column format that is incredibly hard to work with. for example let's say my _schema collection looks like this (redundant data truncated):

"table": "inventory",
"fields": [
    { "name": "item", "type": "varchar" },
    { "name": "size", "type": "row("h" double, "w" double)" }
]

Now, the size of the first item in my inventory collection is two-dimensional as it's a piece of paper, and therefore doesn't have a depth field, however, the second item is three-dimensional and therefore requires the _schema to be:

"table": "inventory",
"fields": [
    { "name": "item", "type": "varchar" },
    { "name": "size", "type": "row("h" double, "w" double, "d" double)" }
]

Now, I have a jsonSchema file for my collection that looks like this:

{
  "properties": {
    "item": { "bsonType": "string" },
    "size": {
      "bsonType": "object",
      "properties": {
        "h": { "bsonType": "double" },
        "w": { "bsonType": "double" },
        "d": { "bsonType": "double" }
      }
    }
  }
} 

there is no "comfortable" way of converting a jsonSchema intro trino's row(..., ...) data type

for the long term, I suggest adding a property to the MongoDB Trino plugin that will allow the user to insert a jsonSchema per-collection - and the _schema document for said collection will be created from it

Is there any other solution that I can implement right now to solve this issue?

[siegenthalerroger]

Honestly, it'd also just be interesting to be able to tell Trino to not only take one sample document but rather choose multiple or even all documents in order to build it's schema.