0

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

Here's the flow:

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": ""
        }
}

Create the table in mysql:

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

Loading the file into mysql:

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

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.