0

At work, we supply files for other services. Size of them are between 5mo and 500mo. We want to use Json instead of XML, but i m wondering how our customers could upload those files in an easy way in their database, Oracle or Mysql. I mean, i can t find on the web APi or functions or tools, in Mysql or oracle, to do that. I know that it s easy to work data by data to load a small Json file, decode each object or array and put them at the right place in database. But is there an other way to do this, like sqlloader in Oracle ? And if so, size of our file aren t they too big to produce JSON file, in JAVA for example ? I guess it might be difficult to do this load job automatically, especially because of arrays like this : 

{"employees":[
    {"firstName":"John", "lastName":"Doe", "salaryHistory":[1000,2000,3000]},
    {"firstName":"Anna", "lastName":"Smith", "salaryHistory":[500,800]},
    {"firstName":"Peter", "lastName":"Jones", "salaryHistory":[400]}
]}

where salaryHistory must produce problems because their sizes are different, and data are not madatoryly in the same table. Any ideas or help would be welcomed !

Edit

i m looking for a solution to put each data in the good column of a table, i don t need to store a Json structure in a single column of simple table.

like this : table employees : column are id, FirstName, lastName and table salaryHistory : column are id, order, salary

and each data must go in the good column like "John" in firstname, "Doe" in lastname, then "1000" in a new row of table salaryHistory , "2000" in another new row of salaryHistory and so on.

Stephane K
  • 45
  • 1
  • 9
  • Possible duplicate of [MySQL parameterized queries](https://stackoverflow.com/questions/775296/mysql-parameterized-queries) – bitsobits Jan 15 '19 at 12:11

1 Answers1

0

Starting with MySQL 5.7 there is a new data type: JSON. Take a look here for more details.

Example for Oracle 12c:

create table transactions (
   id      number not null primary key,
   trans_msg       clob,
   constraint 
   check_json check (trans_msg is json)
);

regular insert:

insert into transactions
values
(
   sys_guid(),
   systimestamp,
'{
   "TransId"         :   3,
   "TransDate"       :   "01-JAN-2015",
   "TransTime"       :   "10:05:00",
   "TransType"       :   "Deposit",
   "AccountNumber"   :   125,
   "AccountName"     :   "Smith, Jane",
   "TransAmount"     :   300.00,
   "Location"        :   "website",
   "CashierId"       :   null,
   "ATMDetails"      :   null,
   "WebDetails"      : {
                     "URL"   : "www.proligence.com/acme/dep.htm"
              },
   "Source"   :   "Transfer",
   "TransferDetails"   :
            {
               "FromBankRouting"   : "012345678",
               "FromAccountNo"   : "1234567890",
               "FromAccountType"   : "Checking"
            }
}'
)
/

SQL*Loader control file and data file:

load data into table transactions
fields terminated by ','
(
  trans_id    sequence(max,1),
  fname       filler char(80),
  trans_body  lobfile(fname) terminated by EOF
)
Che Burashka
  • 46
  • 5
  • This is the starting link for JSON in Oracle 12c Database: https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246 – Che Burashka Feb 03 '16 at 09:32
  • i thank you for your reply but i must have been messy in my question and your answer is not what i m looking for. But you helped me to modify my post to be more accurate. Have a nice day ! – Stephane K Feb 03 '16 at 09:51