使用LOAD DATA导入JSON数据并在导入时指定日期数据类型 [英] Using LOAD DATA to import JSON data and specify date data type on import

问题描述

我正在sql中导入JSON数据，并希望将人类可读的日期数据转换为yyyy-mm-dd格式.

I'm importing JSON data in sql and would like like to convert human readable date data to yyyy-mm-dd format.

以下是流程:

我正在使用以下存储在/home/name/json_data/sample.json

I'm using the following json file stored under /home/name/json_data/sample.json

{
    "price": null,
    "sale_list": [
        {
            "buyer": "SmackMe089",
            "date": "April 29th 2019 21:20:50",
            "id": "1234",
            "item_desc": ""
        }
}

在mysql中创建表:

Create the table in mysql:

CREATE TABLE example_table (
         id INT NOT NULL AUTO_INCREMENT,
         json_data JSON NOT NULL,
         PRIMARY KEY (id)
);

将文件加载到mysql中:

Loading the file into mysql:

LOAD DATA INFILE '/home/name/json_data/sample.json' INTO TABLE example_table (json_data);

当可以将数据加载到表中时，将日期数据转换为yyyy-mm-dd吗?在查询时，我能够为列分配数据类型，但不确定如何正确转换日期数据.可以在查询过程中还是在导入过程中做到这一点?

When loading data into the table is possible convert the date data to yyyy-mm-dd? On querying I am able to assign data types to the columns, but not sure how to properly convert date data. Can this be done during querying or during import?

EX:

select test.* from example_table,
JSON_TABLE(json_data, '$.sale_list[*]' COLUMNS (
buyer VARCHAR(40) PATH '$.buyer',
date VARCHAR(40) PATH '$.date',
id VARCHAR(40) PATH '$.id',
item_desc VARCHAR(40) PATH '$.item_desc'
) ) test;

推荐答案

我了解您使用的是MySQL 8.0.4或更高版本，因为您使用的是

I understand that you are using MySQL 8.0.4 or higher because you are using JSON_TABLE() function.

一种选择是使用请参见 dbfiddle .

更新

在加载数据中，将Date转换为选定的格式，但是，句子稍微复杂些，要加载的文件格式必须保持结构.此外，在新的日期格式中，诸如时间之类的信息将丢失:

In LOAD DATA it is possible to convert the Date to the selected format, however, the sentence is somewhat more complicated and the file format to load must maintain the structure. In addition, in the new Date format, information such as Time will be lost:

LOAD DATA INFILE '/path/to/file/sample.json'
INTO TABLE `example_table` (@`json`)
SET `json_data` =
  JSON_REPLACE(
    @`json`,
    '$.sale_list[0].date',
    LEFT(
      STR_TO_DATE(
        JSON_UNQUOTE(
          JSON_EXTRACT(
            @`json`,
            '$.sale_list[0].date'
          )
        ),
      '%M %D %Y %H:%i:%s'),
    10)
  );

这篇关于使用LOAD DATA导入JSON数据并在导入时指定日期数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！