SQL storing as JSON vs adding fields to table

Question

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.

zedfoxus · Accepted Answer · 2019-04-22 18:46:29Z

3

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.

answered Apr 22, 2019 at 18:46

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DBro Over a year ago

Downsides to storing as JSON are that 1) you will require more storage space because now you have to repeatedly store curly braces, colons, and the name of every property 2) it adds complexity to whatever is saving or displaying the JSON string, because now you have to build, parse, and extract the contents 3) if you ever DO need to search the properties in a query, it becomes slow, painful, and error-prone in SQL. It would have to be a pretty compelling reason for me to choose this option over storing the values separately in SQL.

Upamanyu Sundaram Over a year ago

Hi thanks for replying. How big of an issue are schema changes? How could that adversely affect our db?

zedfoxus Over a year ago

Schema changes can be easy or hard, depending on the process implemented and size of databases. Adding a not null column with default value on a large table of millions of records could take time with record locking. If you want fewer touches to the schema, JSON helps.

KeukenkastjeXYZ · Accepted Answer · 2019-04-22 18:48:59Z

2

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.

answered Apr 22, 2019 at 18:48

KeukenkastjeXYZ

2191 silver badge4 bronze badges

Collectives™ on Stack Overflow

SQL storing as JSON vs adding fields to table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related