MySQL Shell 8.0 (part of MySQL 8.0)
MySQL Shell's JSON import utility
util.importJSON()
, 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 8.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, the first in MySQL Shell's JavaScript
mode and the second in MySQL Shell's Python mode, 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 in MySQL Shell's JavaScript mode 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 in MySQL Shell's JavaScript mode 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 in MySQL Shell's JavaScript mode 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.