Storing JSON in database vs. having a new column for each key

by

Updated 4 June 2017

Given that this question/answer have gained some popularity, I figured it was worth an update.

When this question was originally posted, MySQL had no support for JSON data types and the support in PostgreSQL was in its infancy. Since 5.7, MySQL now supports a JSON data type (in a binary storage format), and PostgreSQL JSONB has matured significantly. Both products provide performant JSON types that can store arbitrary documents, including support for indexing specific keys of the JSON object.

However, I still stand by my original statement that your default preference, when using a relational database, should still be column-per-value. Relational databases are still built on the assumption of that the data within them will be fairly well normalized. The query planner has better optimization information when looking at columns than when looking at keys in a JSON document. Foreign keys can be created between columns (but not between keys in JSON documents). Importantly: if the majority of your schema is volatile enough to justify using JSON, you might want to at least consider if a relational database is the right choice.

That said, few applications are perfectly relational or document-oriented. Most applications have some mix of both. Here are some examples where I personally have found JSON useful in a relational database:

  • When storing email addresses and phone numbers for a contact, where storing them as values in a JSON array is much easier to manage than multiple separate tables

  • Saving arbitrary key/value user preferences (where the value can be boolean, textual, or numeric, and you don’t want to have separate columns for different data types)

  • Storing configuration data that has no defined schema (if you’re building Zapier, or IFTTT and need to store configuration data for each integration)

I’m sure there are others as well, but these are just a few quick examples.

Original Answer

If you really want to be able to add as many fields as you want with no limitation (other than an arbitrary document size limit), consider a NoSQL solution such as MongoDB.

For relational databases: use one column per value. Putting a JSON blob in a column makes it virtually impossible to query (and painfully slow when you actually find a query that works).

Relational databases take advantage of data types when indexing, and are intended to be implemented with a normalized structure.

As a side note: this isn’t to say you should never store JSON in a relational database. If you’re adding true metadata, or if your JSON is describing information that does not need to be queried and is only used for display, it may be overkill to create a separate column for all of the data points.

More Related Contents:

  1. How to speed up sql queries ? Indexes?
  2. SQL Print the name of all Employees together with the name of their supervisor
  3. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  4. 1052: Column ‘id’ in field list is ambiguous
  5. How to do a batch insert in MySQL
  6. Count the occurrences of DISTINCT values
  7. SQL: Repeat a result row multiple times, and number the rows
  8. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  9. Is there a Profiler equivalent for MySql? [closed]
  10. Cast int to varchar
  11. Why do results from a SQL query not come back in the order I expect?
  12. Import SQL file into mysql
  13. How to skip certain database tables with mysqldump?
  14. “where 1=1” statement [duplicate]
  15. MySql views performance [closed]
  16. How to set a MySQL row to READ-ONLY?
  17. MySQL: LAST_INSERT_ID() returns 0
  18. Using reserved words in column names
  19. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  20. Calculating a Moving Average MySQL?
  21. how to cast the hexadecimal to varchar(datetime)?
  22. Strings as Primary Keys in MYSQL Database [closed]
  23. MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
  24. What is the difference between SQL and MySQL? [closed]
  25. How do I list all the columns in a table?
  26. Selecting distinct 2 columns combination in mysql
  27. Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server
  28. How to store a one to many relation in MySQL database?
  29. Why in SQL NULL can’t match with NULL?
  30. MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;

Leave a Comment