`sqlGetSchema`

On this page

Syntax

Parameters
Output
Example

Note

Beta

The support for SQL format queries is available as a Beta feature. The feature and the corresponding documentation may change at any time during the Beta stage.

The sqlGetSchema command retrieves the schema stored for the specified collection or view.

Syntax

db.runCommand({sqlGetSchema: "<collection-name>|<view-name>"})

Parameters

Parameter	Type	Description	Necessity
`<collection-name>`	string	Name of the collection for which to retrieve the schema. Either the collection name or view name is required.	Conditional
`<view-name>`	string	Name of the view for which to retrieve the schema. Either the view name or collection name is required.	Conditional

Output

The command returns the following output if the collection or view does not have a schema.

{ "ok" : 1, "metadata" : { }, "schema" : { } }

The command returns output similar to the following if the collection or view has a schema.

{
  "ok": 1,
  "metadata": {
        "description": "<description>"
  },
  "schema": {
        "version": NumberLong(1),
        "jsonSchema": {}
  }
}

The metadata.description field describes how the schema was set for the collection. Value can be one of the following:

generated automatically by Atlas Data Lake: Indicates that the schema was automatically generated by Atlas Data Lake.

set using sqlGenerateSchema with setSchemas = true: Indicates that the schema was set by the sqlGenerateSchema command because the setSchema option was set to true.

set using sqlSetSchema: Indicates that the schema was set using the sqlSetSchema command.

The schema document contains the following fields:

Parameter	Type	Description
`schema.version`	integer	Format version of the schema. Value is always 1.
`schema.jsonSchema`	document	JSON schema of the collection or view. The JSON schema can contain the following fields : `bsonType` `properties` `items` To learn more about these fields, see JSON Schema Keywords.

Example

Consider a collection named egData in a database named sampleDB with the following documents:

{"a": {"b": {"c": [1, 2, 3]}}, "s": 1}
{"a": {"b": {"c": [4, 5, 6]}}, "s": 2}
{"a": {"b": [7, 8, 9]}, "s": 3}
{"a": {"b": {"c": []}}, "s": 4}
{"a": {"b": {"c": "hello"}}, "s": 5}
{"a": {"b": {"c": {"d": 1}}}, "s": 6}
{"a": {"b": {"c": null}}}
{"s": 7}

The following command retrieves the schema stored for the egData collection:

db.runCommand({sqlGetSchema: "egData" })

The previous command returns the following output. For more information on the fields in the output, see Output.

{
      "ok" : 1,
      "metadata" : {
        "description" : "set using sqlGenerateSchema with setSchemas = true"
      },
      "schema" : {
        "version" : NumberLong(1),
        "jsonSchema" : {
              "bsonType" : [
                "object"
              ],
              "properties" : {
                "a" : {
                      "bsonType" : [
                        "object"
                      ],
                      "properties" : {
                        "b" : {
                              "bsonType" : [
                                "object",
                                      "array"
                                ],
                                "properties" : {
                                      "c" : {
                                        "bsonType" : [
                                              "array",
                                              "string",
                                              "object",
                                              "null"
                                        ],
                                        "properties" : {
                                              "d" : {
                                                "bsonType" : [
                                                      "int"
                                                ]
                                              }
                                        },
                                        "items" : {
                                              "bsonType" : [
                                                "int"
                                              ]
                                        }
                                      }
                                },
                                "items" : {
                                      "bsonType" : [
                                        "int"
                                      ]
                                }
                              }
                        }
                      },
                      "s" : {
                        "bsonType" : [
                              "int",
                              "object"
                        ]
                      }
                }
              }
        }
      }
}

← sqlSetSchemaConnect to Data Lake with JDBC Driver →