Querying with SQL

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

Syntax¶

db.runCommand({sqlSetSchema: "<collection-name>|<view-name>", schema: {"version": 1, "jsonSchema": <jsonSchema>})

Parameters¶

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

Output¶

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"

Examples¶

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.

Set Schema Example¶

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 }

Delete Schema Example¶

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

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