Join Operation with Nosql

JOINS in NoSQL databases

I don't know about Solr, but MongoDB indeed doesn't have joins. You can embed related data inside its parent entity (it is quite natural for User object to embed user roles).

If, however, because of this embedding document might outgrow its limit (currently 16M per document), then you have no choice but to store this related data in separate documents (either as individual documents or grouped into several document-batches).

If you store data separately from its main entity, then, naturally, you have to perform more than one query to fetch all the data. Not sure how Solr will like this.

MongoDB supports in-place partial updating of documents.

Firestore replicating a SQL Join for noSQL and Flutter

Unfortunately, there is no JOIN clause in Cloud Firestore nor in others NoSQL databases. In Firestore queries are shallow. This means that they only get items from the collection that the query is run against. There is no way to get documents from two top-level collection in a single query. Firestore doesn't support queries across different collections in one go. A single query may only use properties of documents in a single collection.

So the most simple solution I can think of is to query the database to get the uid of a user from the profile collection. Once you have that id, make another database call (inside the callback), and get the corresponding data that you need from the connection collection using the following query:

stream: Firestore.instance.collection('connection').where('uid', isEqualTo: "xyc4567").snapshots(),

Another solution would be to create a subcollection named connection under each user and add all connection objects beneath it. This practice is called denormalization and is a common practice when it comes to Firebase. If you are new to NoQSL databases, I recommend you see this video, Denormalization is normal with the Firebase Database for a better understanding. It is for Firebase realtime database but same rules apply to Cloud Firestore.

Also, when you are duplicating data, there is one thing that need to keep in mind. In the same way you are adding data, you need to maintain it. With other words, if you want to update/detele an item, you need to do it in every place that it exists.

How to do inner joining in MongoDB?

As Tiramisu wrote this looks like schema issue.

You can make a manual inner join, by removing documents where $lookup returned empty array.

....
{$lookup... as myArray},
{$match: {"myArray":{$ne:[]}}},
{$lookup... as myArray2},
{$match: {"myArray2":{$ne:[]}}},

schema change

I personally will go for schema update, like this:

db.User.find({})
{
   ID : 1,
   USER_NAME : "John",
   password : "pass"
   roles:[{ID : 1,  ROLE_NAME : "admin"}]
}

db.ROLE.find({})
{
   ID : 1,
   ROLE_NAME : "admin"
},

How to join two arrays from different nosql collections?

There are several possible approaches, depending on how you generate the invoice:

1. If you generate the invoice on the fly and you don't need to keep an history of the prices at the time of generating this invoice, you could first query the different items composing the order, then loop over these items to fetch, for each one, its price from the menu node (reference list of items)

2. If you want to keep the price of an item at the time of ordering (item prices may change over the time): In your GUI you probably compose your order by selecting the item from a list that you have fetched from the database (e.g. through a drop-down list that you populate with values from the db). In this case, when fetching the list of available items you would fetch not only the name and category, but also the price. When you write your order to the database, you add, in the item object, the price of the item, resulting in a structure like the following:

      "orders" : [ null, {
        "comment" : "Bitte, Lassen Sie die Pizza geschnitten.",
        "date" : "2018-06-01 07:22:10",
        "item" : [ {
        "name" : "Tomatensuppe",
        "price" : "3,9",
        "quantity" : "2",
        "size" : ""
      }, {
        "name" : "Estragoncremesuppe",
        "price" : "4,5",
        "quantity" : "1",
        "size" : ""
      } ]
   

   In this case you generate your invoice by querying only once the corresponding order node, all the needed info is available.

   Note that this solution is probably the cheapest one in terms of cost of querying, since you have to query the list of items anyway and adding the extra "price" data item to the order items could more or less be considered neglectable compare to the other data that compose an order. Of course this is an estimate and, if necessary, should be calculated accurately!!

Implementing the second approach with Vue.js is quite easy if you fetch the menu items list as an array of objects.

MongoDB and joins

It's no join since the relationship will only be evaluated when needed. A join (in a SQL database) on the other hand will resolve relationships and return them as if they were a single table (you "join two tables into one").

You can read more about DBRef here:
http://docs.mongodb.org/manual/applications/database-references/

There are two possible solutions for resolving references. One is to do it manually, as you have almost described. Just save a document's _id in another document's other_id, then write your own function to resolve the relationship. The other solution is to use DBRefs as described on the manual page above, which will make MongoDB resolve the relationship client-side on demand. Which solution you choose does not matter so much because both methods will resolve the relationship client-side (note that a SQL database resolves joins on the server-side).

---

Related Topics