Navigation

sqlSetSchema

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 sqlSetSchema command sets or deletes the schema for a collection or view. The command uses the provided schema to create the relational schema. The command does not validate the schema provided with the command against the data in the collection.

db.runCommand({sqlSetSchema: "<collection-name>|<view-name>", schema: {"version": 1, "jsonSchema": <jsonSchema>})
Parameter
Type
Description
Necessity
<collection-name>
string
Name of the collection for which to set the schema. Either the collection name or view name is required.
Conditional
<view-name>
string
Name of the view for which to set the schema. Either the view name or collection name is required.
Conditional
schema
document
The format version of the schema and the JSON schema to set the schema for the collection or view or an empty document to remove the schema for the collection or view.
Required

The command returns the following output if the command succeeds.

{ "ok" : 1 }

You can verify that the command succeeded by running the sqlGetSchema command. The sqlGetSchema command metadata.description should contain the following value:

"set using sqlSetSchema"

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 examples below use the sqlSetSchema command to set and remove schema for the above collection.

The following sqlSetSchema command sets the schema for the egData collection.

db.runCommand({sqlSetSchema : "egData", "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" ], "properties" : { "b" : { "bsonType" : [ "object" ], "properties" : { "c" : { "bsonType" : [ "object" ], "properties" : { "d" : { "bsonType" : [ "array" ], "items" : [ { "bsonType" : [ "string" ] } ] } } } } } } } } } }})

The previous command returns the following output.

{ "ok" : 1 }

The following sqlSetSchema command removes the schema for the egData collection.

db.runCommand({sqlSetSchema: "egData", schema: {} })

The previous command returns the following output.

{ "ok" : 1 }
Give Feedback