3

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?

Improve this question
2
  • 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. Commented Jul 8, 2016 at 20:25
  • 3
    Unless you are on 5.7, see this 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. Commented Jul 8, 2016 at 20:31

2 Answers 2

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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 !

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.