2

I am building an application and while building my database layer, i have the question should we create a column where i can store multiple items as JSON, or create columns for each item i want to store?

When should we store JSON in the database and when should we not?

What are the advantages and disadvantages of storing JSON in the database?

so an example, for all the columns that don’t need to be searched or indexed, would it be more optimal to store their values in a JSON array, or should I stick to creating normalized data tables where I have multiple columns?

ffmsingh
  • 141
  • 2
  • 6
  • 2
    json is just text. it has the same "cost" as any other text. If you **NEVER** need to deal with individual chunks of data within the json, then there's zero cost. it's just text. If you DO need to access individual chunks, then cost is massive - mysql doesn't know what json is, which means you have to use string operations to access those chunks of data, or suck the json into the client and decode there. – Marc B Jul 08 '16 at 20:25
  • 3
    Unless you are on 5.7, see [this](http://stackoverflow.com/a/33661590) and Document Validation for 5.7 ... I think the community would benefit greatly by someone putting together an unbiased typical business scenario with and without json. A few million rows. Show the metrics. – Drew Jul 08 '16 at 20:31

2 Answers2

2

On a relational database, you should always try to normalize. From this perspective seen, it is simple wrong to store a json string.

As long as you don't want to manipulate or query the content of the JSON, I see no performance impact. This means you only set and get by primary key.

If you general want to store your objects, and it is not a special case, I would also suggest you to take a look at a NoSQL Database like MongoDB. It is for storing and receiving objects. Maybe it is something for you, maybe not.

That said, my rule of thumb is:

  • If you end up with a table with only one row, use XML/JSON (ConfigID|XML/JSON)
  • If you end up with multiple rows, do normalization
Christian Gollhardt
  • 16,510
  • 17
  • 74
  • 111
0

With your example : for all the columns that don’t need to be searched or indexed, you could absolutely use JSON data in MySQL.

Logically, it allows saving the storage memory rather than normalized data tables. So you could use well the JSON storage in MySQL in this case.

In addtion, I want to say you more detail for the possible storage types here: JSON or ARRAYS . For me, I use usually serialized arrays rather than JSON storage. ARRAYS, it's more performant for unserialized a object class / arrays but limited using just only for PHP (with function serialized && unserialized in PHP). So the choice for you :

Are you using your datas only with PHP ? If yes : arrays, if no : JSON.

You can see more the discussion following : JSON vs. Serialized Array in database . Good luck !

Community
  • 1
  • 1
SanjiMika
  • 2,664
  • 19
  • 19