How to join two arrays from different nosql collections?

Question

I want to merge two json collection with vue.

{
  "menu" : {
    "-LEJeLN1gNToI0U_xCGO" : {
      "available" : true,
      "category" : "Suppe",
      "name" : "Tomatensuppe",
      "price" : "3,9"
    },
    "-LEJeQHCIatuhn-L7EXB" : {
      "available" : true,
      "category" : "Suppe",
      "name" : "Zwiebelsuppe",
      "price" : "3,9"
    },
    "-LEJeXA33ljBP1B27nFV" : {
      "available" : true,
      "category" : "Suppe",
      "name" : "Knoblauchcremesuppe",
      "price" : "3,9"
    },
    "-LEJed9vwMdX8kL8-A50" : {
      "available" : true,
      "category" : "Suppe",
      "name" : "Frittatensuppe",
      "price" : "3,9"
    },
    "-LEJehsBE2CQJAmseZ9j" : {
      "available" : true,
      "category" : "Suppe",
      "name" : "Minestrone",
      "price" : "3,9"
    },
    "-LEJeoLVcF3-w84gD5Bj" : {
      "available" : true,
      "category" : "Suppe",
      "name" : "Bouillon mit Ei",
      "price" : "3,9"
    },
    "-LEJezGoiQRVz2zTRZe6" : {
      "available" : true,
      "category" : "Suppe",
      "name" : "Estragoncremesuppe",
      "price" : "4,5"
    }
  },
  "orders" : [ null, {
    "comment" : "Bitte, Lassen Sie die Pizza geschnitten.",
    "date" : "2018-06-01 07:22:10",
    "item" : [ {
      "name" : "Tomatensuppe",
      "quantity" : "2",
      "size" : ""
    }, {
      "name" : "suppe",
      "quantity" : "1",
      "size" : ""
    } ]
  }, {
    "comment" : "Geben Sie Brot dazu",
    "date" : "2018-03-19 15:22:20",
    "item" : [ {
      "name" : "Minestrone",
      "quantity" : "3",
      "size" : ""
    }, {
      "name" : "Tomatensuppe",
      "quantity" : "2",
      "size" : ""
    } ]
  } ]
}

The problem is that I collected data into separate collections, but I need something like SQL Join to merge some collections. For example: I have an order collection which contains the orders in a restaurant. Now, I have to create a bill, so I need to add somehow the price of the given article from the menu collection.

Is there a way to do that, or should I build an other db structure? Thank you!

Answer 1

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.