Is our use case suitable for a NoSQL Database solution? We are currently running a Python web app querying a SQL MariaDB (through SQLAlchemy). Unfortunately we have run into some hard limitations of MariaDB (number of joins are limited to 61) and are looking for alternatives. Our database is not very large (generally under 2GB and growing slowly) and the entries could actually very well be broken down to simple json objects, which made us look at a NoSQL solution.

Initially this seemed like a great solution. Queries were super fast (as the DB fit into RAM) and we were happy (for a few days).

I came here to seek advice if the NoSQL DB is a recommended solution for our needs, though. So let's talk about the data:

The nature of our data is that multiple group of entries / rows generally reference back to the same object, pretty much as if you had multiple versions that relate to a single object.

Assume a document entry could look like this:

_id: 5a31...
description: Object
    location: "XYZ"
    name: "ABC"
    status: "A"
    m_nr: null
    k_nr: null
    city: "QWE"
    high_value: 17
    right_value: 71
more_data: Object
    number: 101
    interval: 1
    next_date: "2016-01-16T00:00:00Z"
    last_date: null
    status: null
classification: Object
    priority_value: "?"
    redundancy_value: "?"
    active_value: "0"


Imagine the description.location will often need to be grouped and sorted so that I can only display the $last entry for each of these grouped entries. A common query may look like this (in MongoDB):

db.getCollection('a').aggregate(
    [{ $sort:
        {"description.location": 1}
     },
     { $group:
        {_id: "$description.location"}
     }]
)


I have unfortunately found that this particular query takes very long, when the DB does not fit into memory - even with an index for description.location present in the MongoDB. (For some reason the $group aggregation operation never appears to be using the available index, while $sort actually does use it).

Either way, is this data / layout / query strategy something that resonates well with a NoSQL DB ?
I see your interest in JSON objects.


  Our database is not very large (generally under 2GB and growing
  slowly) and the entries could actually very well be broken down to
  simple json objects, which made us look at a NoSQL solution.


MongoDB is better suited for huge databases, in your case, I suggest  PostgreSQL.

You can use JSON types in PostgreSQL: https://www.postgresql.org/docs/current/static/datatype-json.html

Should be easy enough to migrate your app to PostgreSQL, because it's SQL all the way, and I doubt that you will find that stupid join limit.


  Either way, is this data / layout / query strategy something that
  resonates well with a NoSQL DB ?


Performance has costs when using NoSQL, many times, you will have to modify your code, your database schema, your server configuration. Are you prepared to pay the price?

Answering your question, I try to avoid MongoDB aggregation, preferring to manually aggregate on the spot (my source code is my SQL), but I do it for small ranges. If doing the entire table/collection, would be better to use Map-Reduce or Aggregation or custom code? It depends, who is doing the job faster?

Most of the time, people will advise to denormalize your data, but doing this can cause another range of issues. Again, pay the price.