Блог

Главная - Вопросы по программированию - Как преобразовать json в sql с помощью оболочки mysql

Как преобразовать json в sql с помощью оболочки mysql

#mysql #sql #json #mysql-shell

Вопрос:

У меня есть файл JSON, который выглядит так

 {
  "msb": [
{
  "ville": "Le Mans",
  "pays": "FRANCE",
  "debutMatch": "2021-03-16T20:00:00 02:00",
  "dateFin": "2021-03-16T22:00:00 02:00",
  "meta": {
    "cree": "2021-03-10T16:22:36.223 01:00",
    "modofie": "2021-03-12T16:29:25.223 01:00"
  },
  "etat": "public",
  "titre": "MSB "Orléans" - 16 mars 2021",
  "url": "https://msb.fr/index.php/component/content/article/114-competitions/jeep-elite/5107-msb-lyon-villeurbanne-105-96-ap?Itemid=101",
  "nomOrganisation": "le-mans-sarthe-basket"
},
{
  "ville": "Le Mans",
  "pays": "FRANCE",
  "debutMatch": "2021-03-16T20:00:00 02:00",
  "dateFin": "2021-03-16T22:00:00 02:00",
   "meta": {
    "cree": "2021-03-10T16:22:36.223 01:00",
    "modofie": "2021-03-12T16:29:25.223 01:00"
  },
  "etat": "public",
  "titre": "MSB "Boulazac" - 06 avril 2021",
  "url": "https://msb.fr",
  "nomOrganisation": "le-mans-sarthe-basket"
},
{
  "ville": "Le Mans",
  "pays": "FRANCE",
  "debutMatch": "2021-04-06T20:00:00 02:00",
  "dateFin": "2021-04-06T22:00:00 02:00",
  "logo": {
    "logo": "logo_msb_180.png",
    "url": "https://msb.fr/images/teams/logo/logo_msb_180.png"
  },
  "meta": {
    "cree": "2021-03-10T16:22:36.223 01:00",
    "modofie": "2021-03-12T16:29:25.223 01:00"
  },
  "etat": "public",
  "titre": "MSB "Orléans" - 06 avril 2021",
  "url": "https://msb.fr",
  "nomOrganisation": "le-mans-sarthe-basket"
},
{
  "ville": "Le Mans",
  "pays": "FRANCE",
  "debutMatch": "2021-03-20T20:00:00 02:00",
  "dateFin": "2021-03-20T22:00:00 02:00",
   "meta": {
    "cree": "2021-03-15T16:22:36.223 01:00",
    "modofie": "2021-03-17T16:29:25.223 01:00"
  },
  "etat": "public",
  "titre": "MSB "Orléans" - 20 mars 2021",
  "url": "https://msb.fr",
  "nomOrganisation": "le-mans-sarthe-basket"
}
],
 "page_total": {
    "pageZ": 42,
    "Total_compte": 215,
    "pageI": 1,
    "pageT": 6,
    "numeroIdentifiant": "164784656259969"
  }
}

Как конвертировать вышеуказанный файл в SQL с помощью оболочки mysql

Я попробовал несколько команд для импорта своего файла :

Сценарий создания таблицы :

 CREATE DATABASE database_name;
CREATE TABLE joomla.table_name (
   doc JSON,
   id INTEGER AUTO_INCREMENT PRIMARY KEY 
) CHARSET utf8mb4 ENGINE=InnoDB;

Подключение к mysqlx

 mysqlsh --mysqlx -u root -h localhost -P 33060 -p
password : 1234

Изменить схему по умолчанию

 shell.connect( {scheme:'database_name', user:'user', host:'localhost', port:33060} )

Команда для импорта файла json в таблицу имя_таблицы

 util.importJson("/test.json", {schema: "database_name", table: "table_name", tableColumn: "doc"});

Мы хотели бы иметь в нашей базе данных следующий результат

 CREATE TABLE IF NOT EXISTS table_name(
    `msb_ville` VARCHAR(7) CHARACTER SET utf8,
    `msb_pays` VARCHAR(6) CHARACTER SET utf8,
    `msb_debutMatch` VARCHAR(25) CHARACTER SET utf8,
    `msb_dateFin` VARCHAR(25) CHARACTER SET utf8,
    `msb_meta_cree` VARCHAR(29) CHARACTER SET utf8,
    `msb_meta_modofie` VARCHAR(29) CHARACTER SET utf8,
    `msb_etat` VARCHAR(6) CHARACTER SET utf8,
    `msb_titre` VARCHAR(30) CHARACTER SET utf8,
    `msb_url` VARCHAR(126) CHARACTER SET utf8,
    `msb_nomOrganisation` VARCHAR(21) CHARACTER SET utf8,
    `msb_logo_logo` VARCHAR(16) CHARACTER SET utf8,
    `msb_logo_url` VARCHAR(49) CHARACTER SET utf8,
    `page_total_pageZ` INT,
    `page_total_Total_compte` INT,
    `page_total_pageI` INT,
    `page_total_pageT` INT,
    `page_total_numeroIdentifiant` BIGINT
);
INSERT INTO test VALUES
    ('Le Mans','FRANCE','2021-03-16T20:00:00 02:00','2021-03-16T22:00:00 02:00','2021-03-10T16:22:36.223 01:00','2021-03-12T16:29:25.223 01:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-16T20:00:00 02:00','2021-03-16T22:00:00 02:00',NULL,NULL,'public','MSB "Orléans" - 16 mars 2021','https://msb.fr/index.php/component/content/article/114-competitions/jeep-elite/5107-msb-lyon-villeurbanne-105-96-ap?Itemid=101','le-mans-sarthe-basket',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-16T20:00:00 02:00','2021-03-16T22:00:00 02:00','2021-03-10T16:22:36.223 01:00','2021-03-12T16:29:25.223 01:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-16T20:00:00 02:00','2021-03-16T22:00:00 02:00',NULL,NULL,'public','MSB "Boulazac" - 06 avril 2021','https://msb.fr','le-mans-sarthe-basket',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-04-06T20:00:00 02:00','2021-04-06T22:00:00 02:00',NULL,NULL,NULL,NULL,NULL,NULL,'logo_msb_180.png','https://msb.fr/images/teams/logo/logo_msb_180.png',NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-04-06T20:00:00 02:00','2021-04-06T22:00:00 02:00','2021-03-10T16:22:36.223 01:00','2021-03-12T16:29:25.223 01:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-04-06T20:00:00 02:00','2021-04-06T22:00:00 02:00',NULL,NULL,'public','MSB "Orléans" - 06 avril 2021','https://msb.fr','le-mans-sarthe-basket',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-20T20:00:00 02:00','2021-03-20T22:00:00 02:00','2021-03-15T16:22:36.223 01:00','2021-03-17T16:29:25.223 01:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    ('Le Mans','FRANCE','2021-03-20T20:00:00 02:00','2021-03-20T22:00:00 02:00',NULL,NULL,'public','MSB "Orléans" - 20 mars 2021','https://msb.fr','le-mans-sarthe-basket',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
    (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,42,215,1,6,164784656259969);
Метки: