Question

MONGODB Question (Similar to JSON) NOSQL QUESTION

1. Describe a scenario and write a query that uses any two of these functions: $concat, $substr, $toLower, $toUpper

2. Describe a scenario and write a query that uses any two of these functions: $add, $divide, $mod, $multiply, $subtract

3. Describe a scenario and write a query that uses $redact, $$descend and $$prune command

Answer 1

Answer 1:

Scenario:

Consider a student collection that has the following documents.

{ "student_id" : 1, "name" : "ABC1", date: "19Q1", "description" : "passed" }
{ "student_id" : 2, "name" : "ABC2", date: "20Q2", "description" : "passed" }
{ "student_id" : 3, "name" : "ABC3", date: "18Q3", "description" : null }

$substr:

It returns a substring of a string. It has the following syntax:

{ $substr: [ <string>, <start>, <length> ] }

Using this expression to separate the date value into a year and a quarter:

db.student.aggregate(
   [
     {
       $project:
          {
            name: 1,
            yearSubstring: { $substr: [ "$date", 0, 2 ] },
            quarterSubtring: { $substr: [ "$date", 2, -1 ] }
          }
      }
   ]
)

The expression returns the following results:

{ "student_id" : 1, "name" : "ABC1", "yearSubstring" : "19", "quarterSubtring" : "Q1" }
{ "student_id" : 2, "name" : "ABC2", "yearSubstring" : "20", "quarterSubtring" : "Q2" }
{ "student_id" : 3, "name" : "XYZ1", "yearSubstring" : "18", "quarterSubtring" : "Q3" }

$concat:

It concatenates strings. It has the following syntax:

{ $concat: [ <expression1>, <expression2>, ... ] }

Using this expression to concatenate the name field and the description field with a ” - ” delimiter.

db.student.aggregate(
   [
      { $project: { nameDescription: { $concat: [ "$name", " - ", "$description" ] } } }
   ]
)

The expression returns the following results:

{ "student_id" : 1, "nameDescription" : "ABC1 - passed" }
{ "student_id" : 2, "nameDescription" : "ABC2 - passed" }
{ "student_id" : 3, "nameDescription" : null }

Answer 2:

Scenario:

Consider an employee collection with the following documents:

{ "emp_id" : 1, "project" : "A", "hours" : 80, "tasks" : 7, "salary" : 10000, "tax" : 1000 }
{ "emp_id" : 2, "project" : "B", "hours" : 40, "tasks" : 4, "salary" : 20000, "tax" : 2000 }

$mod:

It divides one number by another and returns the remainder. It has the following syntax:

{ $mod: [ <expression1>, <expression2> ] }

Using this expression to return the remainder of the hours field divided by the tasks field:

db.employee.aggregate(
   [
     { $project: { remainder: { $mod: [ "$hours", "$tasks" ] } } }
   ]
)

The expression returns the following results:

{ "emp_id" : 1, "remainder" : 3 }
{ "emp_id" : 2, "remainder" : 0 }

$subtract:

It subtracts two numbers to return the difference. It has the following syntax:

{ $subtract: [ <expression1>, <expression2> ] }

Using this expression to compute the total salary in hand by subtracting the tax from the salary.

db.employee.aggregate( [ { $project: { emp_id: 1, salary_in_hand: { $subtract: [ "$salary", "$tax" ] } } } ] )

The expression returns the following results:

{ "emp_id" : 1, "salary_in_hand" : 9000 }
{ "emp_id" : 2, "salary_in_hand" : 18000 }

Answer 3:

Scenario:

Consider a forecast collection that contains documents where the tags field lists the different access values for that document/embedded document level; i.e. a value of [ "R", "W" ] specifies either "R" or "W" can access the data.

$redact:

It restricts the contents of the documents based on information stored in the documents themselves. The syntax is:

{ $redact: <expression> }

$$DESCEND:

It is a system variable and $redact returns the fields at the current document level, excluding embedded documents.

$$PRUNE:

It is a system variable and $redact excludes all fields at this current document/embedded document level.

{
  _id: 1,
  title: "123 Department Report",
  tags: [ "R", "W" ],
  year: 2014,
  subsections: [
    {
      subtitle: "Section 1: Overview",
      tags: [ "X", "R" ],
      content:  "Section 1: This is the content of section 1."
    },
    {
      subtitle: "Section 2: Analysis",
      tags: [ "W" ],
      content: "Section 2: This is the content of section 2."
    },
    {
      subtitle: "Section 3: Budgeting",
      tags: [ "A" ],
      content: {
        text: "Section 3: This is the content of section3.",
        tags: [ "X" ]
      }
    }
  ]
}

A user has access to view information with either the tag "R" or "W". To run a query on all documents with year 2014 for this user, include a $redact stage as in the following:

var userAccess = [ "R", "W" ];
db.forecasts.aggregate(
   [
     { $match: { year: 2014 } },
     { $redact: {
        $cond: {
           if: { $gt: [ { $size: { $setIntersection: [ "$tags", userAccess ] } }, 0 ] },
           then: "$$DESCEND",
           else: "$$PRUNE"
         }
       }
     }
   ]
);

The expression returns the following “redacted” document:

{
  "_id" : 1,
  "title" : "123 Department Report",
  "tags" : [ "R", "W" ],
  "year" : 2014,
  "subsections" : [
    {
      "subtitle" : "Section 1: Overview",
      "tags" : [ "X", "R" ],
      "content" : "Section 1: This is the content of section 1."
    },
    {
      "subtitle" : "Section 2: Analysis",
      "tags" : [ "W" ],
      "content" : "Section 2: This is the content of section 2."
    }
  ]
}