Database – Would a NoSQL DB be more efficient than a relational DB for storing JSON objects

I think your question really boils down to: When should I use a NoSQL approach vs. RDBMS? You settled on JSON early (a NoSQL-ish decision), perhaps because you've got Ajax consumers.

The answer of course to when to use NoSQL approaches vs. RDBMS's is basically about what type of data you're working with and what consumers you anticipate having. If your data is essentially relational (fairly flat hierarchies, no weird data types like images or audio, predictable relationships between the schemas that can be easily described in keys), and your consumers are anticipated to eventually include people who want to do Business Intelligence queries (ad hoc querying), then an RDBMS is the way to go. It's fairly easy to turn a query into a JSON representation, so it doesn't significantly burden your Ajax consumers -- it just adds a little transformation coding into your endpoints (REST/SOAP/whatever). Conversely, if your data is very hierarchical (deep schemas), contains weird data types like images, audio, video, etc., there are few relationships between entities, and you know that your end users will not be doing BI, then NoSQL/storing JSON may be appropriate.

Of course, even these general guidelines aren't rock solid. The reason Google developed Google File System, MapReduce (work which was used by Doug Cutting to build Hadoop at Yahoo) and later BigQuery (a NoSQL oriented [schemaless] way of managing large scale data) was precisely because they had a lot of ad hoc BI requests, and they couldn't get relational approaches to scale up to the tera/peta/exa/zetta/yotta scales they were trying to manage. The only practical approach was to scale out, sacrificing some of ad-hoc-query user friendliness that an RDBMS provides, and substituting a simple algorithm (MapReduce) that could be coded fairly easily for any given query.

Given your schema above, my question would basically be: Why wouldn't you use an RDBMS? I don't see much of a reason not to. Our profession is supposed to be engineering oriented, not fashion oriented, so our instinct should be to pick the easiest solution that works, right? I mean, your endpoints may have to do a little translation if your consumers are Ajaxy, but your data looks very flat and it seems likely that business users are going to want to do all kinds of ad hoc querying on things like music events (Which event was most attended within 50 miles of our capital city last year?)

'Go not to the elves for counsel, for they will say both no and yes.' -- Frodo

NoSQL isn't a very well defined term and all the solutions that run under this name have very different features, so a lot may be possible or not depending on what exactly you are planning to do with it.

Basically you could use some of the more general solutions like maybe MongoDB or Cassandra to simply replace your current relational database. In some cases this makes more sense in others less, but it will work once your team got used to it. Certain things will be easier then, others will be more difficult and you must weight those options against each other and decide (which often enough will mean that there are no advantages big enough and the simple fact that everybody in the team feels most comfortable with relationals and SQL will make the decision easy)

Other NoSQL solutions that are more specialised are not really good candidates to replace your relational DB, like graph databases or simple key value stores. So lets from here talk mainly about those databases that are at least to some degree similar to relational databases.

Scenario 1

Where I work we have exactly this scenario, though quite more complex with a lot of different attributes per article. Some of those attributes in hierarchies like Apple -> iPad -> Air.

The data is still stored in a relational database. But: searching this in real time became a pain. With SQL it was slow and code would have been terribly complex. Selects over many tables, with the additional option to exclude certain attributes like "not blue".

In this case Apache Solr or Elastic Search are a solution. Though of course data is duplicated from the relational database.

But from here our experience with this kind of document store showed that it can handle certain problems very well and we will consider to replace part of the existing relational structure with some other kind of storage. So not the whole database where we also store all the transactional data like orders etc, but for example take out all the attribute information which can be handled much better in the aggregate like data structures of NoSQL.

Scenario 2

Difficult to say, since what you describe is most likely only a very small part of your user handling. Having schemaless storage is an advantage with many NoSQL databases. But some relational databases allow to store such data too (as long as you don't need to query it via SQL in most cases).

Cassandra for example would allow you to define column families in such a case, where your first set of attributes would be one such family and the variable attributes another one.

As somebody said: NoSQL is less about storage and more about querying. So the question is what will be the typical use case for those queries.

A typical problem would be the transactional data here. If you want to store orders, one way would be a schema where users and their orders form an aggregate (kind of user document that contains the orders as subdocuments). This would make getting a user together with his orders very simple and fast, but would make it very difficult to retrieve all orders from last month for sales statistics.

Also strengths of NoSQL solutions are that it can be easier to run them on multiple clusters if you have to work with very large datasets.

Conclusion: Both your scenarios could be modelled with certain NoSQL solutions, but I don't think that (assuming they have to run in a larger environment) they really justify a large extra effort in learning, training and implementation and maybe some other additional disadvantages because both are not specific enough to really leverage the strengths of NoSQL. At least not in that simple form you describe it. Things may become very different once some aspects you describe would be very, very prominent in your usage scenario, like in scenario one the attribute data becomes very complex or in scenario two the variable fields become the largest part of data you store with every user.