How to import specific fields from objects from a JSON file to MYSQL 8.0

user_9565180 0
Viewed 150

With MySql 8 you can import json data directly with jsondata flag with the --import command. This is the official link: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-json-import-command.html

But how do you import specific fields in the json data to specific column of an already defined table.
For eg: If you have a countries table with (id, code, name) and a json file with structure like 

[
 {
  id: 1,
  code: "foo",
  name: "bar",
  otherField1: "baz",
  otherField2: "baz2" 
 },
 {
  ...
 }
]
mysql
1 Answers

Do it in 2 steps. First you import the JSON file into a staging table. Then you insert into your final table from the staging table

Something like

create table country_staging json_text VARCHAR(1000);

mysqlsh user@localhost:33062 --import /jsonpath/countries.json countries_staging jsondata --schema=mydb


INSERT INTO countries (id, code, name, otherfield1, otherfield2)
SELECT JSON_EXTRACT(json_text, "$.id"), 
       JSON_EXTRACT(json_text, "$.code"),
       JSON_EXTRACT(json_text, "$.name"),
       JSON_EXTRACT(json_text, "$.otherfield1"),
       JSON_EXTRACT(json_text, "$.otherfield2")
FROM countries_staging
user_41975050
Related