As a rule, modern RDBMSs support three general types of data:
- Tabular data, where the data schema is known to the DB engine. This is the type of data RDBMSs can do the most with in terms of querying, organizing, joining, etc.
- Opaque data, typically stored in some type of BLOB. The DB engine can’t do much to qualify or otherwise query this data other than to get its size or fetch individual byte-sequences out of it. But it can be stored and fetched by an RDBMS and applications can process BLOB data as they see fit.
- Data with in-line structure, primarily JSON data. Support for JSON data is a fairly new thing and different database engines have varying support for it, ranging from quite excellent support in PostgreSQL to being barely-better-than-a-blob in others.
It’s worth mentioning that all three types of data can be intermingled in tables, so you can store a JSON in a column and have some descriptive properties of your JSON as separate columns of the table that you can use as a PRIMARY KEY or other search keys. Sure, this may involve a bit of denormalization, but it may improve search performance dramatically, particularly if your DB engine doesn’t have very good native JSON support.
Source: Greg Kemnitz