CODEWITHSUNDEEP
jsonmariadb
TooNetCreation
TooNetCreation

Reputation: 269

How to import JSON values inside MySQL (10.2.36-MariaDB) table?

I have the following JSON file :

{
  "ID": 5464015,
  "CUSTOMER_ID": 1088020,
  "CUSOTMER_NAME": "My customer 1"
}
{
  "ID": 5220812,
  "CUSTOMER_ID": 523323,
  "CUSOTMER_NAME": "My customer 2"
}
{
  "ID": 5205039,
  "CUSTOMER_ID": 1934806,
  "CUSOTMER_NAME": "My customer 3"
}

From a shell script, I would like to import these values into a MariaDB table (MariaDB Server version : 10.2.36-MariaDB) with the related columns already created :

But for CUSTOMER_NAME, I don't want to import double quotes at the beginning and at the end of the value. Is there a simple way to do it?

Or if not possible, If I have a txt or csv file like this :

5464015,1088020,"My customer 1"
5220812,523323,"My customer 2"
5205039,1934806,"My customer 3"

How to import it?

Many thanks

Upvotes: 0

Views: 5261

Answers (3)

Akina
Akina

Reputation: 42622

The solution which must work on 10.2.36-MariaDB (all used constructions are legal for this version):

CREATE TABLE test (ID INT, CUSTOMER_ID INT, CUSTOMER_NAME VARCHAR(255))
WITH RECURSIVE
cte1 AS ( SELECT LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/json.txt') jsondata ),
cte2 AS ( SELECT 1 level, CAST(jsondata AS CHAR) oneobject, jsondata 
          FROM cte1
          UNION ALL
          SELECT level + 1,
                 TRIM(SUBSTRING(jsondata FROM 1 FOR 2 + LOCATE('}', jsondata))),
                 TRIM(SUBSTRING(jsondata FROM 1 + LOCATE('}', jsondata) FOR LENGTH(jsondata)))
          FROM cte2
          WHERE jsondata != '' )
SELECT oneobject->>"$.ID" ID,
       oneobject->>"$.CUSTOMER_ID" CUSTOMER_ID,
       oneobject->>"$.CUSTOMER_NAME" CUSTOMER_NAME
FROM cte2 WHERE level > 1;

Tested on MySQL 8.0.16 (I have no available MariaDB now):

enter image description here

The content of json.txt file matches shown in the question (misprint in attribute name edited).

enter image description here

PS. Of course the SELECT itself may be used for to insert the data into existing table.

Upvotes: 1

Akina
Akina

Reputation: 42622

CREATE TABLE test (ID INT, CUSTOMER_ID INT, CUSTOMER_NAME VARCHAR(255));

SET @data := '
[  { "ID": 5464015,
     "CUSTOMER_ID": 1088020,
     "CUSTOMER_NAME": "My customer 1"
   },
   { "ID": 5220812,
     "CUSTOMER_ID": 523323,
     "CUSTOMER_NAME": "My customer 2"
   },
   { "ID": 5205039,
     "CUSTOMER_ID": 1934806,
     "CUSTOMER_NAME": "My customer 3"
   }
]
';

INSERT INTO test
SELECT *
FROM JSON_TABLE(@data,
                "$[*]" COLUMNS( ID INT PATH "$.ID",
                                CUSTOMER_ID INT PATH "$.CUSTOMER_ID",
                                CUSTOMER_NAME VARCHAR(255) PATH "$.CUSTOMER_NAME")
               ) AS jsontable;

SELECT * FROM test;
ID CUSTOMER_ID CUSTOMER_NAME
5464015 1088020 My customer 1
5220812 523323 My customer 2
5205039 1934806 My customer 3

db<>fiddle here

Upvotes: 1

Corby Jurgens
Corby Jurgens

Reputation: 86

If you have access to php a simple script is a good method, as it can turn json into an array (and automatically remove said quotes around text) and then you can decide what columns in the json equate to what mysql columns.

Depending on your mysql version you may have access to this utility to inport json from command line https://mysqlserverteam.com/import-json-to-mysql-made-easy-with-the-mysql-shell/ But it may not work if your columns don't match perfectly with the MySQL columns ( I belieivd it is not case sensitive however )

Upvotes: 0

Related Questions