Friday, January 4, 2013

MySQL JSON import / export tools updated

A user of mysqlimport. Josh Baird, reminded me of a feature which I should have added from teh start, but which was forgotten about. The deal is that when you put a bunch of JSON objects in a file, you have a couple of options on how to do this.

The most obvious is maybe to export as a JSON array of objects, like this:
[
{"id":1, "name": "Geraint Watkins"},
{"id":2, "name": "Kim Wilson"}
]
But this is not what mysqljsoninport supported and this is not how, say, MongoDB exports JSON by default. The reason is that for large amount if data this is cumbersome, as what is in the file is actually one big JSON object containing all the data. This is difficult to parse, requires that a lot of data is read and that the object in whole is kept in memory, unless some clever processing is done. And if we are clever, this is still not effective. Rather, what was supported by mysqljsonimport and how MongoDB exports to JSON is as multiple objects without separators, i.e. you read an object, processit, and then you read some optional blankspace until you reach another object, like this:

{"id":1, "name": "Geraint Watkins"}
{"id":2, "name": "Kim Wilson"}

The latter is more effective, but often the former is used also. So mysqljsonimport now supports both formats, and mysqlexport can optionally export as a single JSON array of objects in a file.

Download the most recent version from Sourcefore: mysqlimport 1.5 and mysqlexport 1.2

Cheers
/Karlsson

1 comment:

Céd said...

Hi Anders, thx for this useful tool.

Tell me, is it possible to load a json file with this kind of format :

{"data":{"status":{"key1":"value1","key2":"value2"}}}

just for loaded key1 and key2 in base ?

Thx
Cédric