In a previous post, I talked about using MySQL Shell to import JSON data into a MySQL Document Store. But what if you have JSON data that you need to import into a column into an existing table? Fear not; the importJSON() utility can handle this as well.
This post assumes you have access to a MySQL instance and have installed MySQL Shell.
Setup
Before we get started, we need to get out test data. We can get the JSON used in this demo at this GitHub repo. If we open the file named data.json, we will see it is not an array of objects. Instead, it is a list of JSON objects delimited by line breaks. This format is consistent with data that is exported from MongoDB.
Next, we need to create a table to import the data.
create table my_table ( id INT auto_increment, json_data JSON, PRIMARY KEY (id) );
Connecting to MySQL Shell
For the rest of this post, we will be using MySQL Shell. So, let’s fire it up by running this command:
mysqlsh
We should see something similar to this image:
Now, we need to connect to our MySQL Instance by running the following command:
\c mysqlx://{user}:{password}@{server addresss}
Where {user} is our MySQL username, {password} is the password for this user, and {server address} is the IP address or domain name of the server to which we are connecting.
When the connection is successful, we will see the following:
Note: In my example, I am using a user (not root) that does not need a password when connecting to localhost.
Next, we connect to the schema where we created the table above. In this example, the schema is named import_demo. To connect to this schema, we use the following command
\u import_demo
When we have connected to the schema, we should see the following:
Importing The Data
We are now ready to import our data. We use the util.importJson() method as follows:
util.importJson("{absolute path to JSON file}", {schema: "{schema name}", table: "{table name}", tableColumn: "{column name}"});
Where {absolute path to JSON file} is the absolute path to the file we wish to import, {schema name} is the name of the schema to which we will import the data, {table name} is the name of the table, and {column name} is the name of the column into which we want to import our data.
Note: When using Windows, you need to use / as the path delimiter instead of \. If you do not, you will receive an error that the file cannot be found.
After we run the command, we should see a message that tells us how many items were imported and how long the process took.
Checking Our Work
We can quickly check the import by switching to SQL mode using this command:
\sql
When we do this, we should see that we are now in SQL mode.
Now we run a select statement to retrieve some of our new data:
select * from my_table limit 2;
This query will return two rows of data, showing that the json_data column is now populated.
Wrap up
As we can see, MySQL Shell offers helpful utilities for importing data. We can use these utilities to import data into an existing column in a table or, as discussed here, into a MySQL Document Store.