Monday, May 13, 2013

Version 1.6 of mysqljsonimport now available

Yes, finally! This took some time, but I have been so busy with other things, work-related as well as domestic, that I just haven't had the time for this. But finally version 1.6 is available for download from sourceforge. The downloads is as usual the autoconf enabled source code and PDF documentation in PDF.

So, what is new you ask, well there is one big new feature which took a lot more effort than I expected. When this program was written at first, I still have the table/use use in mind. What this means is that I visioned JSON objects to be mapped to a table. This is not how programmers view JSON, but this is how data is viewed in many databases, even NoSQL ones such as MongoDB. So I wanted an import tool for simple row-structured JSON objects.

Now, there is a different way to look at things, which is to see the data in the JSON file as objects, and each member as one or more rows in a table. This sort of makes up an object (yes, this is very simplistic, but you get the point). So data might look like this:
{"nodename": "server1"
 "users": [{"id": "joe", "name": "Joe bloggs"},
    {"id": "sue", "name": "Sue Bloggs"}
  "hosts" [{"name"; "internal", "address": ""},
    {"name": "external", "address": ""}
{"nodename": "server2"
 "users": [{"id": "dick", "name": "Rickard Bloggs"}
  "hosts" [{"name"; "internal", "address": ""},
    {"name": "external", "address": ""}
Here we would be loading into tables users and hosts and we would load some 7 rows in those two tables. I think what is also clear is that there is a whole bunch of stuff here to make this smarter, like other fields of the object affecting the data that is loaded, either being added to the data or to filter what data is loaded. But none of that is in place right now, for this version, this is just a simple object to table load. The old row-by-row formats are still supported (plain JSON format and Array format).

Also, something cool to add is to add support for MariaDB dynamic colums. I have some ideas here, but I have yet to write the code.

In addition, this release adds a --dry-run option has been added, which allows you to test config files and settings, before starting to load.

I'm planning to write more about MySQL / MariaDB and JSON here eventually, and also about plain JSON, but for now, have fun, take care and happy SQLing.


1 comment:

Darshan Shah said...

Hi Karlsson,

I come across your utility for importing JSON data into mysql database.
I have gone through the document and able to successfully import data from sample files you have provided after few attempts.

Now i want to import the table data where i am getting data into object format.
The way you have mentioned i tried by providing option --object-format while executing the utility but getting error saying "Usage: mysqljsonimport [] [table ...]".

Can you please provide me the sample command to execute and how to provide --object-format and provide the table list in mysqljsonimport utility execute command.

Would really appreciate your support as I am trying to debug this for last couple of days and have tried various options but not able to crack it.

Darshan Shah