JSON is a popular format for storing and exchanging data on the web. It’s also becoming an increasingly common format for database migration from modern apps (such as MixPanel, SalesForce, and Shopify) over to SQL databases. We know because we see a fair amount of JSON to MySQL conversions 😄

So we’ve been thinking - when should you store data with JSON and when should you opt for something else, like SQL?

It all depends on what you need to do

JSON is perfect for storing temporary data that’s consumed by the entity that creates the data. A good example is user-generated data such as filling out a form or information exchange between an API and an app.

Stored JSON data must be text but this means JSON can be used as a data format for any programming language, providing a high level of interoperability. It also means data stored in JSON files are easily sent between servers. This is a bonus for database migration.

A bunch of JSON strings

In JSON, data must also be one of the following values:

  • A string
  • A number
  • An object (JSON object)
  • An array
  • A boolean
  • Null

This leads us to our first limitation: JSON can’t store functions, dates, or undefined data.

When to use something else

If any of the data you need to store or exchange is something other than text or is a function, date, or has no definition then you should use something other than JSON.

You should also opt for something other than JSON if you plan to report on any of the data; or if you plan to pull information back based on something within the JSON. The hierarchical nature of JSON means many DBMS won’t let you query the data. If you still want to use JSON, you’ll need to find some way to flatten it or put it into a normalized schema.

SQLizer can do this for you. Our algorithm ensures hierarchical JSON data is flattened into SQL with no data loss. Our help section has more on how we effectively convert JSON to MySQL.

Having said that, there are some DBMS that allow native JSON (and XML) support. But even so, there’ll need to be some predictability in the JSON schema if you want any kind of querying performance. With other databases such as SQL, this predictability is standard.

Summing it up

It boils down to a simple distinction between temporary data without the need for reporting (JSON) and more permanent data that will likely be used in the future (CSV/SQL). You can use JSON for more permanent data storage, it just means finding someway to normalize or flatten it.

JSON is a great way of storing data and exchanging information at a server level. But when you want more robust, permanent data and need a JSON database migration to MySQL, SQLizer will be here to help you flatten that hierarchical data.

Got a JSON file you need to convert right now? Convert your file!