CODEWITHSUNDEEP
sqljson
Upamanyu Sundaram
Upamanyu Sundaram

Reputation: 149

SQL storing as JSON vs adding fields to table

In an SQL database, is there any benefit over storing display only values that you wont be searching by as a JSON object string instead of adding an extra column for each of the object's properties?

EX:

col1|col2|jsonString
--------------------
1   |1   |{prop1:A,prop2:B,prop3:C}

VS

col1|col2|prop1|prop2|prop3
---------------------------
1   |1   |A    |B    |C

Is one more efficient than the other in terms of memory used or anything else? If you could provide sources that would be really appreciated as well. Thanks.

Upvotes: 1

Views: 1459

Answers (2)

KeukenkastjeXYZ
KeukenkastjeXYZ

Reputation: 219

zedfoxus answer is correct. I want to add that if you plan to store whole JSON documents, you might as well look into a non-relational database like MongoDB, that is designed for purposes like this. The advantages stated by zedfoxus will still be present and databases like MongoDB are optimized to store JSON documents.

Upvotes: 2

zedfoxus
zedfoxus

Reputation: 37059

One benefit of storing information in JSON when you don't intend to search is to avoid schema changes. For example, you have an application that allows a user to store their preferences. Those preferences can grow over time. Different users set different preferences, often leaving some of them as default or empty.

If you were to create a field for each preference, you would have to deal with schema changes whenever a preference has to be added/removed. If you had a JSON field that stored such preferences, you would have much more flexibility in adding/removing preferences.

If your application languages can consume JSON easily, it becomes really easy for the application to ask of preferences for UserID 1 and parse JSON.

Upvotes: 3

Related Questions