MySQL Shell 8.0
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.