MySQL Shell 8.0  /  MySQL Shell Utilities  /  JSON Import Utility

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

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.

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. (Standard input can only be imported with the --import command line invocation of the utility.) options is a dictionary of import options that is required, but can be empty. 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 connection URI 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.

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 is false. This option must be specified and set to true when importing data from MongoDB, because MySQL Server requires the _id value to be converted to the varbinary(32) data type.

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. 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. The _id value is converted from MongoDB's strict mode format into the format required by MySQL Server:

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

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 the --import command, which accepts input from standard input or a file. With the mysqlsh command interface, you invoke the utility as follows:

mysqlsh user@host:port/mydb -- util importJson <path> [options]
or
mysqlsh user@host:port/mydb -- util import-json <path> [options]

For information on this syntax, see Section 4.7, “API Command Line Interface”. For the JSON import utility, specify the parameters as follows:

user

The user name for the user account that is used to run the JSON import utility.

host

The host name for the MySQL server.

port

The port number for MySQL Shell's connection to the MySQL server. The default port for this connection is 33060.

mydb

The name of the target database. When invoking the JSON import utility from the command line, you must specify the target database. You can either specify it in the connection URI, or using an additional --schema command line option.

path

The file path for the file containing the JSON documents to be imported.

options

The --collection, --table, and --tableColumn options specify a target collection or a target table and column. The relationships and defaults when the JSON import utility is invoked using the mysqlsh command interface are the same as when the corresponding options are used in a MySQL Shell session. If you specify none of these options, the utility defaults to using or creating a target collection with the name of the supplied import file (without the file extension).

The --convertBsonOid option converts MongoDB ObjectIDs into the format required by MySQL Server. You must specify this option if you are importing data from MongoDB. Note that this option is not available with the --import command line version of the utility.

The following example imports the JSON documents in the file products.json to the products collection in the mydb database:

mysqlsh user@localhost/mydb -- util importJson products.json --collection=products

The --import command is also available as an alternative to the mysqlsh command interface for command line invocation of the JSON import utility. This command provides a short form syntax without using option names, and it accepts JSON documents from standard input. The syntax is as follows:

mysqlsh user@host:port/mydb --import <path> [target] [tableColumn]

As with the mysqlsh command interface, you must specify the target database, either in the connection URI, or using an additional --schema command line option. The first parameter for the --import command is the file path for the file containing the JSON documents to be imported. To read JSON documents from standard input, specify a dash (-) instead of the file path. The end of the input stream is the end-of-file indicator, which is Ctrl+D on Unix systems and Ctrl+Z on Windows systems.

After specifying the path (or - for standard input), the next parameter is the name of the target collection or table. If standard input is used, you must specify a target.

  • If the target collection or table exists in the specified schema, the JSON documents are imported to it. If the target is a table and you specify a further parameter giving a column name, the specified column is used for the import destination. Otherwise the default column name doc is used, which must be present in the existing table.

  • If the target does not yet exist, the utility defaults to creating a collection with the specified name. To create and import to a table, you must also specify a column name as a further parameter, in which case the utility creates a relational table with the specified table name and imports the data to the specified column.

  • If you have specified a file path but do not specify a target, the utility searches for any existing collection or table in the specified schema that has the name of the supplied import file (without the file extension). If one is found, the documents are imported to it. Note that a table found in this way must have a column named doc (the default column name) in order to be able to accept the import. If no collection or table with the name of the supplied import file is found in the specified schema, the utility creates a collection with that name and imports the documents to it.

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. The schema name is specified using the --schema command line option instead of in the connection URI:

mysqlsh user@localhost:33062 --import /europe/regions.json regions jsondata --schema=mydb

The following example with no target specified imports the JSON documents in the file /europe/regions.json. If no collection or table named regions (the name of the supplied import file without the extension) is found in the specified mydb database, the utility creates a collection named regions and imports the documents to it. If there is already a collection named regions, the utility imports the documents to it. If there is a table named regions with a column named doc (the default column name), the utility imports the documents to that table and column.

mysqlsh user@localhost/mydb --import /europe/regions.json

The following example reads JSON documents from standard input and imports them to a target named territories in the mydb database. For standard input, a target must be specified. If no collection or table named territories is found, the utility creates a collection named territories and imports the documents to it. If you want to create and import the documents to a relational table named territories, you must specify a column name as a further parameter.

mysqlsh user@localhost/mydb --import - territories

MySQL Shell returns a message confirming the parameters for the import, for example, Importing from file "/europe/regions.json" to table `mydb`.`regions` in MySQL Server at 127.0.0.1:33062.

When an 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 process returns zero if the import finished successfully, or a nonzero exit code if there was an error.