11.2.3 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 use standard input and the specified target is a relational table that exists in the specified schema, the documents are imported to it. You can specify a further parameter giving a column name, in which case 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 is not an existing table, the utility searches for any collection with the specified target name, and imports the documents to it. If no such collection is found, the utility creates a collection with the specified target name and imports the documents to it. 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 specify a file path and a target, the utility searches for any collection with the specified target name. If none is found, the utility by default creates a collection with that name and imports the documents to it. To import the file to a table, you must also specify a column name as a further parameter, in which case the utility searches for an existing relational table and imports to it, or creates a relational table with the specified table name and imports the data to the specified column.

• If you specify a file path but do not specify a target, the utility searches for any existing collection 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. If no collection 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.

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