Documentation Home
MySQL Shell 8.0
Related Documentation Download this Manual
PDF (US Ltr) - 0.5Mb
PDF (A4) - 0.5Mb
HTML Download (TGZ) - 102.8Kb
HTML Download (Zip) - 114.7Kb


MySQL Shell 8.0  /  MySQL Shell Utilities  /  JSON Import Utility

7.2 JSON Import Utility

MySQL Shell's JSON import utility, introduced in MySQL Shell 8.0.13, enables you to import JSON documents from a file (or FIFO special file) or standard input to a MySQL Server collection or relational table. The utility checks that the supplied JSON documents are well-formed and inserts them into the target database, removing the need to use multiple INSERT statements or write scripts to achieve this task.

From MySQL Shell 8.0.14, the import utility can process BSON (binary JSON) data types that are represented in JSON documents. The data types used in BSON documents are not all natively supported by JSON, but can be represented using extensions to the JSON format. The import utility can process documents that use JSON extensions to represent BSON data types, convert them to an identical or compatible MySQL representation, and import the data value using that representation. The resulting converted data values can be used in expressions and indexes, and manipulated by SQL statements and X DevAPI functions.

You can import the JSON documents to an existing table or collection or to a new one created for the import. If the target table or collection does not exist in the specified database, it is automatically created by the utility, using a default collection or table structure. The default collection is created by calling the createCollection() function from a schema object. The default table is created as follows:

CREATE TABLE `dbname`.`tablename` (
   target_column JSON,
   id INTEGER AUTO_INCREMENT PRIMARY KEY 
) CHARSET utf8mb4 ENGINE=InnoDB;

The default collection name or table name is the name of the supplied import file (without the file extension), and the default target_column name is doc.

To convert JSON extensions for BSON types into MySQL types, you must specify the convertBsonTypes option when you run the import utility. Additional options are available to control the mapping and conversion for specific BSON data types. If you import documents with JSON extensions for BSON types and do not use this option, the documents are imported in the same way as they are represented in the input file.

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 7.2.3, “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 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 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 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 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.