11.2.1 Running the Utility

The JSON import utility requires an existing X Protocol connection to the server. The utility cannot operate over a classic MySQL protocol connection.

In the MySQL Shell API, the JSON import utility is a function of the util global object, and has the following signature:

importJSON (path, options)

path is a string specifying the file path for the file containing the JSON documents to be imported. This can be a file written to disk, or a FIFO special file (named pipe). Standard input can only be imported with the --import command line invocation of the utility.

options is a dictionary of import options that can be omitted if it is empty. (Before MySQL 8.0.14, the dictionary was required.) The following options are available to specify where and how the JSON documents are imported:

schema: "db_name"

The name of the target database. If you omit this option, MySQL Shell attempts to identify and use the schema name in use for the current session, as specified in a URI-like connection string, \use command, or MySQL Shell option. If the schema name is not specified and cannot be identified from the session, an error is returned.

collection: "collection_name"

The name of the target collection. This is an alternative to specifying a table and column. If the collection does not exist, the utility creates it. If you specify none of the collection, table, or tableColumn options, the utility defaults to using or creating a target collection with the name of the supplied import file (without the file extension).

table: "table_name"

The name of the target table. This is an alternative to specifying a collection. If the table does not exist, the utility creates it.

tableColumn: "column_name"

The name of the column in the target table to which the JSON documents are imported. The specified column must be present in the table if the table already exists. If you specify the table option but omit the tableColumn option, the default column name doc is used. If you specify the tableColumn option but omit the table option, the name of the supplied import file (without the file extension) is used as the table name.

convertBsonTypes: true

Recognizes and converts BSON data types that are represented using extensions to the JSON format. The default for this option is false. When you specify convertBsonTypes: true, each represented BSON type is converted to an identical or compatible MySQL representation, and the data value is imported using that representation. Additional options are available to control the mapping and conversion for specific BSON data types; for a list of these control options and the default type conversions, see Section 11.2.4, “Conversions for Representations of BSON Data Types”. The convertBsonOid option must also be set to true, which is that option's default setting when you specify convertBsonTypes: true. If you import documents with JSON extensions for BSON types and do not use convertBsonTypes: true, the documents are imported in the same way as they are represented in the input file, as embedded JSON documents.

convertBsonOid: true

Recognizes and converts MongoDB ObjectIDs, which are a 12-byte BSON type used as an _id value for documents, represented in MongoDB Extended JSON strict mode. The default for this option is the value of the convertBsonTypes option, so if that option is set to true, MongoDB ObjectIDs are automatically also converted. When importing data from MongoDB, convertBsonOid must always be set to true if you do not convert the BSON types, because MySQL Server requires the _id value to be converted to the varbinary(32) type.

extractOidTime: "field_name"

Recognizes and extracts the timestamp value that is contained in a MongoDB ObjectID in the _id field for a document, and places it into a separate field in the imported data. extractOidTime names the field in the document that contains the timestamp. The timestamp is the first 4 bytes of the ObjectID, which remains unchanged. convertBsonOid: true must be set to use this option, which is the default when convertBsonTypes is set to true.

The following examples, the first in MySQL Shell's JavaScript mode and the second in MySQL Shell's Python mode, import the JSON documents in the file /tmp/products.json to the products collection in the mydb database:

mysql-js> util.importJson("/tmp/products.json", {schema: "mydb", collection: "products"})

mysql-py> util.import_json("/tmp/products.json", {"schema": "mydb", "collection": "products"})

The following example in MySQL Shell's JavaScript mode has no options specified, so the dictionary is omitted. mydb is the active schema for the MySQL Shell session. The utility therefore imports the JSON documents in the file /tmp/stores.json to a collection named stores in the mydb database:

mysql-js> \use mydb
mysql-js> util.importJson("/tmp/stores.json")

The following example in MySQL Shell's JavaScript mode imports the JSON documents in the file /europe/regions.json to the column jsondata in a relational table named regions in the mydb database. BSON data types that are represented in the documents by JSON extensions are converted to a MySQL representation:

mysql-js> util.importJson("/europe/regions.json", {schema: "mydb", table: "regions", tableColumn: "jsondata", convertBsonTypes: true});

The following example in MySQL Shell's JavaScript mode carries out the same import but without converting the JSON representations of the BSON data types to MySQL representations. However, the MongoDB ObjectIDs in the documents are converted as required by MySQL, and their timestamps are also extracted:

mysql-js> util.importJson("/europe/regions.json", {schema: "mydb", table: "regions", tableColumn: "jsondata", convertBsonOid: true, extractOidTime: "idTime"});

When the import is complete, or if the import is stopped partway by the user with Ctrl+C or by an error, a message is returned to the user showing the number of successfully imported JSON documents, and any applicable error message. The function itself returns void, or an exception in case of an error.

The JSON import utility can also be invoked from the command line. Two alternative formats are available for the command line invocation. You can use the mysqlsh command interface, which accepts input only from a file (or FIFO special file), or the --import command, which accepts input from standard input or a file. For instructions, see Section 11.2.2, “Importing JSON Documents With the Mysqlsh Command Interface” or Section 11.2.3, “Importing JSON Documents With the --import Command”.