MySQL Shell 8.0 (part of MySQL 8.0)

7.2.2 Importing JSON documents with the --import command

The --import command is 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] [options]

As with the mysqlsh command interface, you must specify the target database, either in the URI-like connection string, 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 you are importing documents containing representations of BSON (binary JSON) data types, you can also specify the options --convertBsonOid, --extractOidTime=field_name, --convertBsonTypes, and the control options listed in Section 7.2.3, “Conversions for representations of BSON data types”.

The following example reads JSON documents from standard input and imports them to a target named territories in the mydb database. 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

The following example with a file path and a target 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 URI-like connection string:

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

The following example with a file path but 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.

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

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.