Docs Menu

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.

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

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.

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"
]
}
}
}
}
}
}
Give Feedback
© 2021 MongoDB, Inc.

About

  • Careers
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2021 MongoDB, Inc.