Best way to Insert JSON data into MYSQL

Question

I have a JSON data in a text file which contains pair of latitude and longitude,Now I want to store this data into MYSQL DB,How to go about & what's the best approach please suggest.

Here is My JSON data

[{"latlon":{"lng":77.75124312,"lat":12.97123123},"type":"s"}, 
 {"latlon":{"lon":77.73004942455374,"lat":12.98227579706589},"type":"s"},
 {"latlon":{"lon":77.67374449291539,"lat":12.995490063545521},"type":"v"}, 
 {"latlon":{"lon":77.6097147993144,"lat":12.970900929013666},"type":"s"},
 {"latlon":{"lon":77.53933363476645,"lat":12.948316929346504},"type":"s"},
 {"latlon":{"lng":77.48213123,"lat":12.91213213},"type":"s"}
 .
 .
 .
 .
]
The String may go up to 50 points 

Solution 1

Some comments have suggested leaving it in JSON format and storing it in a text column.

I'd suggest that if you have control over your schema, you should store it in two NUMERIC fields - one for latitude, and one for longitude. Fundamentally, that's the data you have. The fact that you've received it using JSON as a container format is irrelevant to the data itself.

If you store it as a latitude and a longitude, it's simpler to query over it, and you can still recreate the JSON later if you want to... but if you end up wanting to fetch it not as JSON, you'll be in a much better position to do so. It also means you're not tying yourself to JSON against future changes.

Personally I think it's always a good idea to mentally separate the intrinsic nature of the data from the way that you happen to receive it. Store the data in whatever format is idiomatic for that storage - so if you're storing a number, use one of the numeric data types. If you're storing a date and time, use datetime, etc. That way you don't end up with an extra layer between you and the data itself every time you want to access it.

This approach also means you're much more likely to spot bad data early on - if you just store the JSON directly without parsing it first, you could later find that the latitude or longitude isn't a valid number. Heck, the fact that your sample data sometimes has lon and sometimes has lng suggests you should do some data cleanup anyway.

Solution 2

This may help you :-

Check out following site it convert your josn data file to sql insert script:

online JSON to SQL data conversion tool

Solution 3

From Mysql 5.7.10 and higher JSON support is native so you can have a JSON data type

Document Validation - Only valid JSON documents can be stored in a JSON column, so you get automatic validation of your data.

Efficient Access - More importantly, when you store a JSON document in a JSON column, it is not stored as a plain text value. Instead, it is stored in an optimized binary format that allows for quicker access to object members and array elements.

Performance - Improve your query performance by creating indexes on values within the JSON columns. This can be achieved with “functional indexes” on virtual columns.

Convenience - The additional inline syntax for JSON columns makes it very natural to integrate Document queries within your SQL. For example (features.feature is a JSON column): SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;