MSDotNetBuddy: May 2023

2023/05/31

Jenkin FreeStyle project for node.js

I have typescript project with express.js i will try to use jenkin with it

I added user jenkins & sangram to sudoer

run 
sudo visudo

then into file add at the bottom of file

jenkins ALL=(ALL) NOPASSWD: ALL
sangram ALL = (ALL) NOPASSWD: ALL

I created a SystemD service for my node app in /etc/systemd/system/nodeapp.service

[Unit]
Description=My Node Application Server

[Service]
ExecStart=sh /home/sangram/Documents/deployments/matrimony-api/runServer.sh

RestartSec=10
Restart=always

StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=nodejs-my-server-example

Environment=PATH=/usr/bin:/usr/local/bin

[Install]
WantedBy=multi-user.target

You can start/stop/restart/enable with following commands

systemctl enable nodeapp.service

systemctl start nodeapp.service
systemctl stop nodeapp.service
systemctl restart nodeapp.service
systemctl status nodeapp.service

I have private repo on github ,i created a legacy token that token need to be added to github url

https://{YOUR_GITHUB_TOKEN}@github.com/gitsangramdesai/matrimony-api.git

The project should be in /home/sangram/Documents/deployments/matrimony-api for deployment

In jenkin freestyle project added following Build Script

tsc
npm run build
sudo cp -r /var/lib/jenkins/workspace/NodejsAPI/dist/* /home/sangram/Documents/deployments/matrimony-api
sudo cp /var/lib/jenkins/workspace/NodejsAPI/.env /home/sangram/Documents/deployments/matrimony-api
sudo su - sangram

sshpass -p "sangram"  ssh -o StrictHostKeyChecking=no -tt sangram@192.168.0.115 <<EOF 
cd /home/sangram/Documents/deployments/matrimony-api
npm i 
exit
EOF

sudo systemctl restart nodeapp.service

I need to convert typescript code to javascript using tsc command ,then need to copy that to deployment directory.Then i need to run npm Install
To Run the project need to restart our newly created service

systemctl restart nodeapp.service

the shell file /home/sangram/Documents/deployments/matrimony-api/runServer.sh is as follows

cd /home/sangram/Documents/deployments/matrimony-api/
npm start

hierachical data in table and queries

Root Node will have parentId zero

create table hierarchy(
    id int primary key,
    name varchar(50),
    value varchar(50),
    parentId int,
    hpath varchar(100)
)


insert into hierarchy(id,name,value,parentId,hpath) values(1,'abc','abc',0,'/1');
insert into hierarchy(id,name,value,parentId,hpath) values(2,'lmn','lmn',1,'/1/2');
insert into hierarchy(id,name,value,parentId,hpath) values(3,'anp','anp',0,'/3');
insert into hierarchy(id,name,value,parentId,hpath) values(4,'abp','abp',3,'/3/4');
insert into hierarchy(id,name,value,parentId,hpath) values(5,'abl','abl',4,'/3/4/5');
insert into hierarchy(id,name,value,parentId,hpath) values(6,'abq','abq',5,'/3/4/5/6');
insert into hierarchy(id,name,value,parentId,hpath) values(7,'abo','ab0',1,'/1/7');
insert into hierarchy(id,name,value,parentId,hpath) values(8,'abk','abk',0,'/8');
insert into hierarchy(id,name,value,parentId,hpath) values(9,'wbl','wbl',4,'/3/4/9');

Find count of childrens for each parent node

select  
    t2.id,
    count(*)  
from 
    hierarchy t1
    inner join hierarchy t2 on t1.parentId = t2.id    
group by 
     t2.id

Find Parent and its child


select parentId,group_concat(id) from hierarchy group by parentId


Find list of childrens of root node

select id,count(*)
from hierarchy
where id in (select id from hierarchy where parentId=0)
group by 
   id;


Find all childrens of node 1

select * from hierarchy where hpath like '/1/%'


Find sibling of 5

select * from hierarchy where parentid=(select parentId from hierarchy 
where id=5);

2023/05/27

Longest Common String -Unefficient Way

function removeChar(str, startIndex, toRemove) {

    var newString = ""
    for (let i = 0; i < str.length; i++) {
        if (i >= startIndex && i < startIndex + toRemove) {
            continue
        } else {
            newString = newString + str[i]
        }
    }
    return newString
}

function GetAllCombinations(s1) {
    var s1Word = new Set()
    var lenofs1 = s1.length;
    var visited = []

    for (let i = 1; i < lenofs1; i++) {
        for (let j = 0; j < lenofs1 - i + 1; j++) {
            var str = removeChar(s1, j, i)

            if (visited.includes(str) == false) {
                visited.push(str)

                //s1Word.add({ "string": str, "i": i, "j": j, "source": s1 })
                s1Word.add(str)

                if (str.length > 1) {
                    let rec = GetAllCombinations(str)
                    s1Word = new Set([...rec, ...s1Word])
                }
            }


        }
    }
    return s1Word
}


function commonChild(s1, s2) {
    let res1 = GetAllCombinations(s1)
    const array1 = Array.from(res1);

    var res2 = GetAllCombinations(s2)
    const array2 = Array.from(res2);

    var common = []
    for (let i = 0; i < array1.length; i++) {
        if (array2.includes(array1[i])) {
            common.push(array1[i])
        }
    }

    var maxLenth = Number.NEGATIVE_INFINITY
    var maxStr = ""
    for (let i = 0; i < common.length; i++) {
        if (maxLenth < common[i].length) {
            maxLenth = common[i].length
            maxStr = common[i]
        }
    }

    return maxStr.length

}



let s1 = "SHINCHAN", s2 = "NOHARAAA";
let res = commonChild(s1, s2)
console.log("Result", res)

Longest Common Child String - Recursive

function commonChild(string1, string2) {

    let m = string1.length;
    let n = string2.length

    if (m <= 0 || n <= 0) {
        return 0;
    }

    for (let i = m - 1; i >= 0; i--) {
        for (let j = n - 1; j >= 0; j--) {
            if (string1[i] == string2[j]) {
                let s1 = string1.substring(0, m - 1);
                let s2 = string2.substring(0, n - 1);
                return commonChild(s1, s2) + 1
            }
            else {
                let s3 = string1.substring(0, m - 1);
                let r1 = commonChild(s3, string2)

                let s4 = string2.substring(0, n - 1);
                let r2 = commonChild(string1, s4)

                let r3 = Math.max(r1, r2)
                return r3;
            }

        }
    }
}


const string1 = "ABCD";
const string2 = "ABDC";
const result = commonChild(string1, string2);
console.log("Result:", result);

2023/05/26

Longest common child String

function commonChild(string1, string2) {

    var n = string1.length
    const dp = Array.from({ length: n + 1 }, () => Array(n + 1).fill(0));

    for (let i = 0; i < n; i++) {
        for (let j = 0; j < n; j++) {
            if (string1[i] == string2[j]) {
                dp[i + 1][j + 1] = dp[i][j] + 1
            } else {
                let r1 = dp[i + 1][j]
                let r2 = dp[i][j + 1]

                dp[i + 1][j + 1] = Math.max(r1, r2)
            }
        }
    }
    console.log("dp:",dp)

    let commonChildLength = dp[n][n]
    let commonChildString = ""

    let i = n;
    let j = n;
    while (i > 0 && j > 0) {
        if (string1[i - 1] == string2[j - 1]) {
            commonChildString = string1[i - 1] + commonChildString
            i--
            j--
        } else if (dp[i][j - 1] >= dp[i - 1][j]) {
            j--
        } else {
            i--
        }
    }

    return { "length": commonChildLength, "string": commonChildString }

    //return commonChildLength
}


const string1 = "HARRY";
const string2 = "SALLY";
const result = commonChild(string1, string2);
console.log("Result:", result);  // Output: "AY"

2023/05/23

MySQL:Table Partitioning

In MySQL, there are four partitioning types:

Range Partioning:
Range partitioning distributes rows to partitions based on column values 
falling within a given range.

List Partitioning:
List partitioning allows you to to be explicit about how rows are 
distributed across the partitions by specifying a list of value for 
the partitioning column of each partition.

Hash Partioning:
Hash partitioning evenly distributed data across all partitions using a 
hash value of a partitioning key.

Key Partioning:
Key partitioning is similar to Hash partitioning but instead of using a
user-defined expression, it uses its own internal hashing function.

    CREATE TABLE mytable2 (
        post_id int DEFAULT NULL,
        viewid int DEFAULT NULL,
        user_id int DEFAULT NULL,
        post_Date datetime DEFAULT NULL
    ) 

    PARTITION BY RANGE (year(post_Date))
    (
        PARTITION P0 VALUES LESS THAN (2020) ,
        PARTITION P1 VALUES LESS THAN (2030) ,
        PARTITION P2 VALUES LESS THAN (2040) ,
        PARTITION P3 VALUES LESS THAN (2050) ,
        PARTITION P4 VALUES LESS THAN MAXVALUE 
    )
 

 By HASH:

    CREATE TABLE employees (
        id INT NOT NULL,
        first_name VARCHAR(30),
        last_name VARCHAR(30),
        hired DATE NOT NULL DEFAULT '2019-09-27',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT,
        store_id INT
    )

    PARTITION BY HASH(store_id) PARTITIONS 4;

By KEY:

    CREATE TABLE simple_customer (
        id INT NOT NULL PRIMARY KEY,
        full_name VARCHAR(40)
    )

    PARTITION BY KEY() PARTITIONS 2;

By LIST:

    CREATE TABLE customers (
        first_name VARCHAR(25),
        last_name VARCHAR(25),
        street_1 VARCHAR(30),
        street_2 VARCHAR(30),
        city VARCHAR(15),
        renewal DATE
    )

    PARTITION BY LIST COLUMNS(city) (
        PARTITION pRegion_1 VALUES IN('Camau', 'Kiengiang', 'Longan'),
        PARTITION pRegion_2 VALUES IN('Dalat', 'Daklak', 'Pleiku'),
        PARTITION pRegion_3 VALUES IN('Danang', 'Hoian', 'Hue'),
        PARTITION pRegion_4 VALUES IN('Hanoi', 'Sapa', 'Ninhbinh')
    );


ALTER SYNTAX:

    CREATE TABLE employees (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30),
        hired DATE NOT NULL DEFAULT '1970-01-01',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT NOT NULL,
        store_id INT NOT NULL
    );


    ALTER TABLE employees PARTITION BY RANGE (store_id) (
        PARTITION p0 VALUES LESS THAN (6),
        PARTITION p1 VALUES LESS THAN (11),
        PARTITION p2 VALUES LESS THAN (16),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );

2023/05/18

MongoDB vs SQL :Group By Query Side By Side

We will look into group by query in mongoDB side side with Mysql 
so that as we are familiar with MySQL we can relate bit easier.
 
 First Insert this documents into mongo db:

 db.sales.insertMany([

    { "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") },
    { "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") },
    { "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") },
    { "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") },
    { "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:12:00Z") }
])

On parrellel we will create equivalent mysql table and insert same data there 

create table sales(
    id integer auto_increment primary key,
    item varchar(50),
    price float,
    quantity int,
    date datetime
)

INSERT INTO sales(item,price,quantity,date) values('abc',10,2,now());
INSERT INTO sales(item,price,quantity,date) values('jkl',20,1,now());
INSERT INTO sales(item,price,quantity,date) values('xyz',5,5,now());
INSERT INTO sales(item,price,quantity,date) values('abc',10,10,now());
INSERT INTO sales(item,price,quantity,date) values('xyz',5,10,now());


Group by only one field:

Mongo Query:

        db.sales.aggregate([
            {
                $group:
                {
                    _id:"$item",
                    avg:{$avg:"$quantity"}
                }
            }
        ])

        Output:
            [
                { _id: 'abc', avg: 6 },
                { _id: 'xyz', avg: 7.5 },
                { _id: 'jkl', avg: 1 }
            ]

Equvalent Mysql Query:
    select item,avg(quantity) from sales group by item;

    Output:
        +------+---------------+
        | item | avg(quantity) |
        +------+---------------+
        | abc  |        6.0000 |
        | jkl  |        1.0000 |
        | xyz  |        7.5000 |
        +------+---------------+


Group by multiple fields:

Mongo Query:
        db.sales.aggregate([
            {
                $group:
                {
                    _id:{"item":"$item","price":"$price"},
                    avg:{$avg:"$quantity"}
                }
            }
        ])

        Output:
        [
            { _id: { item: 'xyz', price: 5 }, avg: 7.5 },
            { _id: { item: 'jkl', price: 20 }, avg: 1 },
            { _id: { item: 'abc', price: 10 }, avg: 6 }
        ]

MySql Query:
        select item,price,avg(quantity) from sales group by item,price;

        Output:
        +------+-------+---------------+
        | item | price | avg(quantity) |
        +------+-------+---------------+
        | abc  |    10 |        6.0000 |
        | jkl  |    20 |        1.0000 |
        | xyz  |     5 |        7.5000 |
        +------+-------+---------------+

Pushing column to Array in Group By:

Mongo Query:
    db.sales.aggregate([
        {
            $group:
            {
                _id:{"item":"$item","price":"$price"},
                qty:{$push:"$quantity"}
            }
        }
    ])

    Output:
        [
            { _id: { item: 'abc', price: 10 }, qty: [ 2, 10 ] },
            { _id: { item: 'jkl', price: 20 }, qty: [ 1 ] },
            { _id: { item: 'xyz', price: 5 }, qty: [ 5, 10 ] }
        ]
Mysql Query:
    select item,price,JSON_ARRAYAGG(quantity) from sales group by item,price;

    Output:
        +------+-------+-------------------------+
        | item | price | JSON_ARRAYAGG(quantity) |
        +------+-------+-------------------------+
        | abc  |    10 | [2, 10]                 |
        | jkl  |    20 | [1]                     |
        | xyz  |     5 | [5, 10]                 |
        +------+-------+-------------------------+



Sum of quantity:

Mongo Query:
    db.sales.aggregate([
        {
            $group:
            {
                _id:{"item":"$item","price":"$price"},
                sum:{$sum:"$quantity"}
            }
        }
    ])
    Output:
            [
                { _id: { item: 'xyz', price: 5 }, sum: 15 },
                { _id: { item: 'jkl', price: 20 }, sum: 1 },
                { _id: { item: 'abc', price: 10 }, sum: 12 }
            ]

Mysql Query:
    select item,price,sum(quantity) from sales group by item,price;

    Output:
        +------+-------+---------------+
        | item | price | sum(quantity) |
        +------+-------+---------------+
        | abc  |    10 |            12 |
        | jkl  |    20 |             1 |
        | xyz  |     5 |            15 |
        +------+-------+---------------+

count of 

MongoDb Query:
    db.sales.aggregate([
        {
            $group:
            {
                _id:{"item":"$item","price":"$price"},
                count:{$sum:1}
            }
        }
    ])

    Output:
        [
            { _id: { item: 'xyz', price: 5 }, count: 2 },
            { _id: { item: 'jkl', price: 20 }, count: 1 },
            { _id: { item: 'abc', price: 10 }, count: 2 }
        ]

MySql Query:
    select item,price,count(*) from sales group by item,price;

    select item,price,count(item) from sales group by item,price;
    select item,price,count(price) from sales group by item,price;

    Output:
        +------+-------+----------+
        | item | price | count(*) |
        +------+-------+----------+
        | abc  |    10 |        2 |
        | jkl  |    20 |        1 |
        | xyz  |     5 |        2 |
        +------+-------+----------+

2023/05/17

MongoDb Vs SQL : All records whose datefield value lies between bounds

Today we look into how to find documents where date lies between two bounds.
We will create SQL Equivalent queries for each mongo query to understand thing
comparatively.

db.EmpJoiningData.insertMany([
      {
         "_id": 001,
         "name": "Sangram Desai",
         "date": ISODate("2022-01-30")
      },
      {
         "_id": 002,
         "name": "Roshan Sawant",
         "date": ISODate("2022-12-28")
      },
      {
         "_id": 003,
         "name": "Meena Verma",
         "date": ISODate("2023-02-12")
      }
   ]
)


Lets create a table which hold equivalenyt data:

create table EmpJoiningData(
   id int auto_increment primary key,
   name varchar(250),
   date datetime
)

INSERT INTO EmpJoiningData(name,date) values('Sangram Desai','2022-01-30');
INSERT INTO EmpJoiningData(name,date) values('Roshan Sawant','2022-12-28');
INSERT INTO EmpJoiningData(name,date) values('Meena Verma','2023-02-12');

Select All Employees who joined in year 2022:
Mongo Query:
      db.EmpJoiningData.find({ 
            date: { 
                     $gte: new Date("2022-01-01"), 
                     $lte: new Date("2023-01-01") 
                  } 
      })

      or

      db.EmpJoiningData.find({ 
            date: { 
                     $gte: new Date("2022-01-01"), 
                     $lte: new Date("2023-01-01") 
                  } 
      },{
         _id:1,
         name:1,
         date:1
       })

     Output:
            { "_id" : 1, "name" : "Sangram Desai", 
                "date" : ISODate("2022-01-30T00:00:00Z") }
            { "_id" : 2, "name" : "Roshan Sawant", 
                "date" : ISODate("2022-12-28T00:00:00Z") }

Mysql Query:
     select * from EmpJoiningData where date between '2022-01-01' and '2023-01-01';

      Output:
               +----+---------------+---------------------+
               | id | name          | date                |
               +----+---------------+---------------------+
               |  1 | Sangram Desai | 2022-01-30 00:00:00 |
               |  2 | Roshan Sawant | 2022-12-28 00:00:00 |
               +----+---------------+---------------------+

      Note:
          Mysql between clause is inclusive of boundry condition so it will 
          also include records which match boundry condition.

Using $and operator

Mongo Query:
   db.EmpJoiningData.find({
      $and: [
         { date: { $gte: new Date("2022-12-01") } },
         { date: { $lte: new Date("2023-03-01") } }
      ]
   })

   or

   db.EmpJoiningData.find({
      $and: [
         { date: { $gte: new Date("2022-12-01") } },
         { date: { $lte: new Date("2023-03-01") } }
      ]
   },{
         _id:1,
         name:1,
         date:1
       })

 output:
   { "_id" : 2, "name" : "Roshan Sawant", 
        "date" : ISODate("2022-12-28T00:00:00Z") }
   { "_id" : 3, "name" : "Meena Verma", 
        "date" : ISODate("2023-02-12T00:00:00Z") }

Mysql Query:
  select * from c where date >= '2022-01-01' and date <= '2023-01-01';

  Output:
      +----+---------------+---------------------+
      | id | name          | date                |
      +----+---------------+---------------------+
      |  1 | Sangram Desai | 2022-01-30 00:00:00 |
      |  2 | Roshan Sawant | 2022-12-28 00:00:00 |
      +----+---------------+---------------------+


When joining month is january

  Mongo Query:
      db.EmpJoiningData.find({ 
         $expr: { $eq: [ { $month: "$date" }, 1 ] } 
      })

      or

      db.EmpJoiningData.find({ 
         $expr: { $eq: [ { $month: "$date" }, 1 ] } 
      },{
         _id:1,
         name:1,
         date:1
       })

      Output:
         { "_id" : 1, "name" : "Sangram Desai", 
            "date" : ISODate("2022-01-30T00:00:00Z") }

   Mysql Query:
      select id,name,date from EmpJoiningData where MONTH(date) =1;

      Output:
            +----+---------------+---------------------+
            | id | name          | date                |
            +----+---------------+---------------------+
            |  1 | Sangram Desai | 2022-01-30 00:00:00 |
            +----+---------------+---------------------+

Using Or Operator:
MongoDb Query:
      db.EmpJoiningData.find({
         $or: [
            { date: { $lt: ISODate("2023-01-01") } },
            { date: { $gt: ISODate("2023-12-01") } }
         ]
      })

      or

       db.EmpJoiningData.find({
         $or: [
            { date: { $lt: ISODate("2023-01-01") } },
            { date: { $gt: ISODate("2023-12-01") } }
         ]
      },{
         _id:1,
         name:1,
         date:1
       })

      Output:
         [
            {
               _id: 1,
               name: 'Sangram Desai',
               date: ISODate('2022-01-30T00:00:00.000Z')
            },
            {
               _id: 2,
               name: 'Roshan Sawant',
               date: ISODate('2022-12-28T00:00:00.000Z')
            }
         ]

Mysql Query:
     select id,name,date from EmpJoiningData where 
            date < '2023-01-01' or date > '2023-12-01';

     Output:
         +----+---------------+---------------------+
         | id | name          | date                |
         +----+---------------+---------------------+
         |  1 | Sangram Desai | 2022-01-30 00:00:00 |
         |  2 | Roshan Sawant | 2022-12-28 00:00:00 |
         +----+---------------+---------------------+

MongoDb Vs SQL : $IN operator

Today we will try to learn about MongoDb $in clause on array.

Though Mysql does not have native array datatype it is possible to store array
element as comma seperated string.it is not possible always emulate all
functionality of MongoDb in Mysql and vice versa let us give it a try.

Ground Up:
We have Mongo Query:
If collection already exist delete it db.inventory.drop()

db.inventory.insertMany([
   { _id:1, item: 'Erasers', quantity: 15, tags: [ 'school', 'home' ] },
   { _id:2 , item: 'Books', quantity: 5, tags: [ 'school', 'storage', 'home' ] },
   { _id:3,item: 'Paper', quantity: 10, tags: [ 'Printer'] },
   { _id:4,item: 'Pencil', quantity: 4, tags: [ 'school'] }
])

Equivalent Mysql Table:

If table already exist drop it 
   drop table inventory;

create table inventory(
    id int auto_increment primary key,
    item varchar(50),
    quantity int,
    tags varchar(250)
);

INSERT INTO inventory(item,quantity,tags) values('Erasers',15,'school, home');
INSERT INTO inventory(item,quantity,tags) values('Books',5,'school,storage, 
   home');
INSERT INTO inventory(item,quantity,tags) values('Paper',10,'Printer');
INSERT INTO inventory(item,quantity,tags) values('Pencil',4,'School');


$in on array Element:

Here is a mongo query to find all documents where tag value contain either one
of the  "school" & "home" may be additional element but thats allowed.


db.inventory.find( { tags: { $in: [ "school","home" ] } } )

output:
    [
        { _id: 1, item: 'Erasers', quantity: 15, tags: [ 'school', 'home' ] },
        {
            _id: 2,
            item: 'Books',
            quantity: 5,
            tags: [ 'school', 'storage', 'home' ]
        },
        { _id: 4, item: 'Pencil', quantity: 4, tags: [ 'school' ] }
    ]


Mysql Query:
   We cant straight forward do this lets get bit crazy.

   select 
        id,
        item,
        quantity,
        tags, 
        INSTR(tags,'school') 
   from 
        inventory 
   where 
        INSTR(tags,'school') = 1 or INSTR(tags,'home') > 0

   Output:
    +----+---------+----------+----------------------+----------------------+
    | id | item    | quantity | tags                 | INSTR(tags,'school') |
    +----+---------+----------+----------------------+----------------------+
    |  1 | Erasers |       15 | school, home         |                    1 |
    |  2 | Books   |        5 | school,storage, home |                    1 |
    |  4 | Pencil  |        4 | School               |                    1 |
    +----+---------+----------+----------------------+----------------------+

$in on non array Element:
Example 1:
    Mongo Query:
        db.inventory.find( { item: { $in: [ "Books","Paper" ] } })

        Output:
        [
                {
                    _id: 2,
                    item: 'Books',
                    quantity: 5,
                    tags: [ 'school', 'storage', 'home' ]
                },
                { _id: 3, item: 'Paper', quantity: 10, tags: [ 'Printer' ] }
        ]

    Mysql Query:

        select id,item,quantity,tags from inventory where 
               item in ('books','paper');

        Output:
            +----+-------+----------+----------------------+
            | id | item  | quantity | tags                 |
            +----+-------+----------+----------------------+
            |  2 | Books |        5 | school,storage, home |
            |  3 | Paper |       10 | Printer              |
            +----+-------+----------+----------------------+

Example 2:
    Mongo Query:
     db.inventory.find( { quantity: { $in: [ 5, 15 ] } })

        Output:
            [
                { _id: 1, item: 'Erasers', quantity: 15, tags: 
                                       [ 'school', 'home' ] },
                {
                    _id: 2,
                    item: 'Books',
                    quantity: 5,
                    tags: [ 'school', 'storage', 'home' ]
                }
            ]

     MySQL query:

        select id,item,quantity,tags from inventory where quantity in (5,15);

        Output:
        +----+---------+----------+----------------------+
        | id | item    | quantity | tags                 |
        +----+---------+----------+----------------------+
        |  1 | Erasers |       15 | school, home         |
        |  2 | Books   |        5 | school,storage, home |
        +----+---------+----------+----------------------+



using $in in update clause:
Mongo Query:
    db.inventory.updateMany(
        { tags: { $in: [ "home"] } },
        { $set: { exclude: false } }
    )

In mysql you are not supposed to update table structure in update query,so we
can't dynamically add new column here. But somewhat similar where we are
emulating $in in current update example.

MySQL Query:
    Update inventory set quantity = quantity +1 where INSTR(tags,"home") > 0


Note :
PostgreSQL, Microsoft SQL Server (MSSQL), and Oracle have native array data
types unlike MySQL.It might get added.

MongoDb:ReplaceOne

Today we will explore replaceOne() in mongoDb.

We are going to use collection "restaurant",so first make sure 
their is no existing collection of this name for that we will
drop existing one with this name.

    db.restaurant.drop()

Now to run our query we need some documents inside our colelction,
so lets add sample documents.

    db.restaurant.insertMany(
        [
            { "_id" : 1, "name" : "Central Perk Cafe", "Borough" : "Manhattan" },
            { "_id" : 2, "name" : "Rock A Feller Bar and Grill", 
                "Borough" : "Queens", "violations" : 2 },
            { "_id" : 3, "name" : "Empire State Pub", "Borough" : "Brooklyn", 
                "violations" : 0 },
            { "_id" : 4, "name" : "Central Perk Cafe", "Borough" : "New York" },
        ]
    )

Replace One without Upsert:

    db.restaurant.replaceOne(
        { "name": "Central Perk Cafe" },
        { "name": "Central Pork Cafe", 
            "Borough": "Mumbai","state":"Maharshtra" } 
    );

    Here mongo will find a document where { "name": "Central Perk Cafe" } and 
    replace the whole document with 
       { "name": "Central Pork Cafe", "Borough": "Mumbai","state":"Maharshtra" } ,
    it should be noted that _id fields will not get updated it will 
    continue to have old value.After Update the matching document becomes.

    {
        _id: 1,
        name: 'Central Pork Cafe',
        Borough: 'Mumbai',
        state: 'Maharshtra'
    }

Note: 
    Actually there are two documents matching filter condition 
    { "name":"Central Perk Cafe" } but we are running replaceOne() 
    so only first matching document will be update others will 
    remain untouched.

    Suppose we want to replace both matching documents then instead of 
    this replaceOne query we should use updateMany.

        db.restaurant.updateMany(
            { "name": "Central Perk Cafe" },
            {$set:{ "name": "Central Pork Cafe", 
                "Borough": "Mumbai","state":"Maharshtra" } }
        );
    This will replace both matching documents.

    But if you try to run following query then it will give you an error

        db.restaurant.updateMany(
            { "name": "Central Pork Cafe" },
            {$set:{ "_id":0,"name": "Central Pork Cafe", 
                "Borough": "Mumbai","state":"Maharshtra" } }
        );

    as the _id property can only be set at the time of insertion if not done 
    at the time of insertion then mongodb create one on its own,once 
    created _id property can not be updated nor can you delete that _id field 
    only option is delete whole document.

Replace One With Upsert:
    db.restaurant.replaceOne(
        { "name" : "Pizza Rat's Pizzariaa" },
        { "_id": 4, "name" : "Pizza Rat's Pizzaria", 
            "Borough" : "Manhattan", "violations" : 8 },
        { upsert: true }
    );

    Here their is no document to match filter condition 
       { "name" : "Pizza Rat's Pizzaria" }
    but we set upsert as true.so it will insert new document with 
    { "_id": 4, "name" : "Pizza Rat's Pizzaria", 
        "Borough" : "Manhattan", "violations" : 8 }

    If in second parameter i.e. 
    { "_id": 4, "name" : "Pizza Rat's Pizzaria", 
        "Borough" : "Manhattan", "violations" : 8 }

    we remove _id field then also _id field will get added but this time 
    it will be having value from mongoDB default method i.e. objectId().

MongoDb:$filter Operator

Suppose we are keeping records of item purchased from our online 
store into mongoDb as follows.

Here we will ensure that their is no existing collection of same 
name so lets drop if exist

db.sales.drop()

Now insert our sales data.

db.sales.insertMany( [
   {
      _id: 0,
      email:"sagar@example.com",
      purchaseTime:new Date("2024-01-01:12:30:00"),
      cartItems: [
         { item_id: 43, quantity: 2, pricePerItem: 10 },
         { item_id: 2, quantity: 1, pricePerItem: 240 }
      ],
      deliveryAddress:{
            city:"Mumbai",
            street:"M k Gandhi Road",
            pin:"400080",
            town:"Vikroli",
            Bldg:"Arun Niwara,Bldg No 167",
            floor:"1st",
            roomNo:"5386" 
      },
      isDelivered:false
   },
    {
      _id: 1,
      email:"swara@example.com",
      purchaseTime:new Date("2024-02-01:10:30:00"),
      cartItems: [
         { item_id: 34, quantity: 2, pricePerItem: 20 },
         { item_id: 56, quantity: 5, pricePerItem: 340 },
         { item_id: 78, quantity: 3, pricePerItem: 540 }
      ],
      deliveryAddress:{
            city:"Mumbai",
            street:"Indiara Gandhi Road",
            pin:"400745",
            town:"Naupada",
            Bldg:"Arun Niwara,Bldg No 167",
            floor:"1st",
            roomNo:"3276" 
      },
      isDelivered:true,
      deliveryDate:new Date("2024-02-05:08:10:00"),
   },
   {
      _id: 2,
      email:"sangram@example.com",
      purchaseTime:new Date("2024-01-05:14:35:00"),
      cartItems: [
         { item_id: 23, quantity: 3, pricePerItem: 110 },
         { item_id: 103, quantity: 4, pricePerItem: 5 },
         { item_id: 38, quantity: 1, pricePerItem: 300 }
      ],
      deliveryAddress:{
            city:"Pune",
            street:"Bose D K  Road",
            pin:"402357",
            town:"Raja Nagar",
            Bldg:"Varun CHS,Bldg No 546",
            floor:"2nd",
            roomNo:"785" 
      },
      isDelivered:false
   },
   {
      _id: 3,
      email:"sachin@example.com",
      purchaseTime:new Date("2024-01-01:08:24:00"),
      cartItems: [
         { item_id: 4, quantity: 1, pricePerItem: 23 },
         { item_id: 2, quantity: 1, pricePerItem: 240 }
      ],
       deliveryAddress:{
            city:"Nashik",
            street:"Jangli Maharaj Road",
            pin:"2345765",
            town:"Vartak Nagar",
            Bldg:"Govardhan Das Apt,Bldg No 796",
            floor:"8nd",
            roomNo:"6885" 
        },
        isDelivered:false
   }
] )

Condition 1:

Suppose Now we want all customer email id along with their cartItem 
where pricePerItem is greater than 100

db.sales.aggregate( [
   {
      $project: {
         email:"$email",
         items: {
            $filter: {
               input: "$cartItems",
               as: "item",
               cond: { $gte: [ "$$item.pricePerItem", 100 ] }
            }
         }
      }
   }
] )

Output:
[
  {
    _id: 0,
    email: 'sagar@example.com',
    items: [ { item_id: 2, quantity: 1, pricePerItem: 240 } ]
  },
  {
    _id: 1,
    email: 'swara@example.com',
    items: [ { item_id: 56, quantity: 5, pricePerItem: 340 } ]
  },
  {
    _id: 2,
    email: 'sangram@example.com',
    items: [
      { item_id: 23, quantity: 3, pricePerItem: 110 },
      { item_id: 38, quantity: 1, pricePerItem: 300 }
    ]
  },
  {
    _id: 3,
    email: 'sachin@example.com',
    items: [ { item_id: 2, quantity: 1, pricePerItem: 240 } ]
  }
]

Condition 2:
Now  we want all customer email id along with their cartItem where pricePerItem
is greater than 100 provided customer is from Mumbai.

db.sales.aggregate( [
    {
      $match: {
         "deliveryAddress.city": "Mumbai"
      }
   },
   {
      $project: {
         email:"$email",
         city:"$deliveryAddress.city",
         items: {
            $filter: {
               input: "$cartItems",
               as: "item",
               cond: { $gte: [ "$$item.pricePerItem", 100 ] }
            }
         }
      }
   }
] )

Output:
        [
            {
                _id: 0,
                email: 'sagar@example.com',
                city: 'Mumbai',
                items: [ { item_id: 2, quantity: 1, pricePerItem: 240 } ]
            },
            {
                _id: 1,
                email: 'swara@example.com',
                city: 'Mumbai',
                items: [ { item_id: 56, quantity: 5, pricePerItem: 340 } ]
            }
        ]


Condition 3:
   Here we want all customer email id along with all their cartItems no
   Other data like purchaseTime or deliveryAddress then

    db.sales.aggregate( [
    {
        $project: {
            email:"$email",
            items: {
                $filter: {
                input: "$cartItems",
                as: "item",
                cond: { 1:1 }
                }
            }
        }
    }
    ] )

Output:
        [
            {
                _id: 0,
                email: 'sagar@example.com',
                items: [
                { item_id: 43, quantity: 2, pricePerItem: 10 },
                { item_id: 2, quantity: 1, pricePerItem: 240 }
                ]
            },
            {
                _id: 1,
                email: 'swara@example.com',
                items: [
                { item_id: 34, quantity: 2, pricePerItem: 20 },
                { item_id: 56, quantity: 5, pricePerItem: 340 }
                ]
            },
            {
                _id: 2,
                email: 'sangram@example.com',
                items: [
                { item_id: 23, quantity: 3, pricePerItem: 110 },
                { item_id: 103, quantity: 4, pricePerItem: 5 },
                { item_id: 38, quantity: 1, pricePerItem: 300 }
                ]
            },
            {
                _id: 3,
                email: 'sachin@example.com',
                items: [
                { item_id: 4, quantity: 1, pricePerItem: 23 },
                { item_id: 2, quantity: 1, pricePerItem: 240 }
                ]
            }
        ]

Condition 4:
  Suppose delivery team ask for list of carts to be sent in mumbai.

    db.sales.aggregate([
        {
            $match: {
                "deliveryAddress.city": "Mumbai",
                "isDelivered":false
            }
        },
        {
            $project: {
                email: 1,
                deliveryAddress: 1
            }
        }
    ])

Output:
        [
            {
                _id: 0,
                email: 'sagar@example.com',
                deliveryAddress: {
                city: 'Mumbai',
                street: 'M k Gandhi Road',
                pin: '400080',
                town: 'Vikroli',
                Bldg: 'Arun Niwara,Bldg No 167',
                floor: '1st',
                roomNo: '5386'
                }
            }
        ]

Condition 5:
Suppose we want all customer email & their cart item classified under 4 sections

below Hundred
below Two Hundred
below Three Hundred
More than Three Hundred

db.sales.aggregate([
  {
    $match: {
      "deliveryAddress.city": "Mumbai"
    }
  },
  {
    $project: {
      email: "$email",
      city: "$deliveryAddress.city",
      belowHundred: {
        $filter: {
          input: "$cartItems",
          as: "item",
          cond: { $lte: ["$$item.pricePerItem", 100] }
        }
      },
      belowTwoHundred: {
        $filter: {
          input: "$cartItems",
          as: "item",
          cond: { $and: [{ $gte: ["$$item.pricePerItem", 100] }, 
            { $lte: ["$$item.pricePerItem", 200] }] }
        }
      },
      belowThreeHundred: {
         $filter: {
           input: "$cartItems",
           as: "item",
           cond: { $and: [{ $gte: ["$$item.pricePerItem", 200] }, 
            { $lte: ["$$item.pricePerItem", 300] }] }
         }
       },
       greaterThanThreeHundred: {
        $filter: {
          input: "$cartItems",
          as: "item",
          cond: { $gte: ["$$item.pricePerItem", 300] }
        }
      }
    }
  }
])

Output:
[
  {
    _id: 0,
    email: 'sagar@example.com',
    city: 'Mumbai',
    belowHundred: [ { item_id: 43, quantity: 2, pricePerItem: 10 } ],
    belowTwoHundred: [],
    belowThreeHundred: [ { item_id: 2, quantity: 1, pricePerItem: 240 } ],
    greaterThanThreeHundred: []
  },
  {
    _id: 1,
    email: 'swara@example.com',
    city: 'Mumbai',
    belowHundred: [ { item_id: 34, quantity: 2, pricePerItem: 20 } ],
    belowTwoHundred: [],
    belowThreeHundred: [],
    greaterThanThreeHundred: [ { item_id: 56, quantity: 5, pricePerItem: 340 } ]
  }
]

Condition 5:
Suppose in our cartItems array each array element corresponds with 
item purchased it contain price per item & total items purchased but 
it does not have item total so now we will write query to generate it.


db.sales.aggregate( [
  {
     $project:{
        email: "$city",
        purchaseTime:"$purchaseTime",
        deliveryAddress:"$deliveryAddress",
        cartItemsWithSumTotal:{
           $map:{
              input:"$cartItems",
              as:"element",
              in:{
                 $mergeObjects: ["$$element",
                  {
                    "ItemTotal": {  $multiply: 
                    ["$$element.quantity","$$element.pricePerItem"] 
                    }
                  }
                ]
              }
           }
        }
     }
  }
])

Output:
[
  {
    _id: 0,
    purchaseTime: ISODate('2024-01-01T07:00:00.000Z'),
    deliveryAddress: {
      city: 'Mumbai',
      street: 'M k Gandhi Road',
      pin: '400080',
      town: 'Vikroli',
      Bldg: 'Arun Niwara,Bldg No 167',
      floor: '1st',
      roomNo: '5386'
    },
    cartItemsWithSumTotal: [
      { item_id: 43, quantity: 2, pricePerItem: 10, ItemTotal: 20 },
      { item_id: 2, quantity: 1, pricePerItem: 240, ItemTotal: 240 }
    ]
  },
  {
    _id: 1,
    purchaseTime: ISODate('2024-02-01T05:00:00.000Z'),
    deliveryAddress: {
      city: 'Mumbai',
      street: 'Indiara Gandhi Road',
      pin: '400745',
      town: 'Naupada',
      Bldg: 'Arun Niwara,Bldg No 167',
      floor: '1st',
      roomNo: '3276'
    },
    cartItemsWithSumTotal: [
      { item_id: 34, quantity: 2, pricePerItem: 20, ItemTotal: 40 },
      { item_id: 56, quantity: 5, pricePerItem: 340, ItemTotal: 1700 },
      { item_id: 78, quantity: 3, pricePerItem: 540, ItemTotal: 1620 }
    ]
  },
  {
    _id: 2,
    purchaseTime: ISODate('2024-01-05T09:05:00.000Z'),
    deliveryAddress: {
      city: 'Pune',
      street: 'Bose D K  Road',
      pin: '402357',
      town: 'Raja Nagar',
      Bldg: 'Varun CHS,Bldg No 546',
      floor: '2nd',
      roomNo: '785'
    },
    cartItemsWithSumTotal: [
      { item_id: 23, quantity: 3, pricePerItem: 110, ItemTotal: 330 },
      { item_id: 103, quantity: 4, pricePerItem: 5, ItemTotal: 20 },
      { item_id: 38, quantity: 1, pricePerItem: 300, ItemTotal: 300 }
    ]
  },
  {
    _id: 3,
    purchaseTime: ISODate('2024-01-01T02:54:00.000Z'),
    deliveryAddress: {
      city: 'Nashik',
      street: 'Jangli Maharaj Road',
      pin: '2345765',
      town: 'Vartak Nagar',
      Bldg: 'Govardhan Das Apt,Bldg No 796',
      floor: '8nd',
      roomNo: '6885'
    },
    cartItemsWithSumTotal: [
      { item_id: 4, quantity: 1, pricePerItem: 23, ItemTotal: 23 },
      { item_id: 2, quantity: 1, pricePerItem: 240, ItemTotal: 240 }
    ]
  }
]


Suppose rather than calculating item total each time we want it to be 
calculated once and saved there in at array element which is  a JSON object.

db.sales.find({"cartItems": { $exists: true, $ne: [] }}).forEach(function(doc) {
    doc.cartItems.forEach(function(item) {
        item.ItemTotalNew = item.quantity * item.pricePerItem;
    });
    db.sales.updateOne(
        { "_id": doc._id },
        { "$set": { "cartItems": doc.cartItems } }
    );
});

Their is also another way to do this,here is it.

It first select all sales document where cartItems is non empty array. 


var bulkUpdate = db.sales.initializeUnorderedBulkOp();
db.sales.find({ "cartItems": { $exists: true, $ne: [] } })
.forEach(function(doc) {
    doc.cartItems.forEach(function(item) {
        item.ItemTotalNew = item.quantity * item.pricePerItem;
    });
    bulkUpdate.find({ "_id": doc._id })
        .updateOne({ "$set": { "cartItems": doc.cartItems } });
});
bulkUpdate.execute();



You can delete this newly added field "ItemTotalNew" by using below query.

db.sales.updateMany(
    {}, // Match all documents
    { $unset: { "cartItems.$[].ItemTotalNew": "" } }
)

If you have field "ItemTotalNew" at root then you can run following query.

db.sales.updateMany(
    {}, // Match all documents
    { $unset: { "ItemTotalNew": "" } }
)

limit on number of element of inner array:

db.sales.aggregate( [
   {
      $project: {
         items: {
            $filter: {
               input: "$cartItems",
               as: "item",
               cond: { $gte: [ "$$item.pricePerItem", 100 ] }
            }
         }
      }
   }
] )

In result of above query items array printed as it is in sense we are not
limiting how many element that array can have.But following query will 
limit its length to 1

db.sales.aggregate( [
   {
      $project: {
         items: {
            $filter: {
               input: "$cartItems",
               as: "item",
               cond: { $gte: [ "$$item.pricePerItem", 100 ] },
               limit: 1
            }
         }
      }
   }
] )

Note :
    We can make exact replica of our collection using $out below is query

    db.sales.aggregate([
        { $out: "purchase" }
    ])

This will create a new collection purchase in same database 
having exactly same documents w.r.t. of sales when the 
command the above run.

MongoDb:$arrayElemAt,$unwind & $filter

Today we will explore $arrayElemAt operator in mongoDb.
Lets create a "candidate" collection,first we will drop if
any pre-existing collection of same name.

db.candidate.drop()

Now lets populate our collection with some sample data to illustrate
$arrayElemAt.

db.candidate.insertOne(
    {
        "name": "Avinash",
        "email":"avinash@example.com",
        "age": 27,
        "company": [
                        {"name":"Techy Hunger" ,"from": new Date("2023-06-10"),"to": new Date("2024-01-01")},
                        {"name":"Back Gemini","from": new Date("2022-08-20"),"to":new Date("2023-05-31")},
                        {"name":"ECS","from": new Date("2022-01-01"),"to":new Date("2022-07-31")}
                  ],
        "skills": ["Java", "MongoDB", "Node", "Angular"],
        
    }
);

$arrayElemAt:

$arrayElemAt returns the element at the specified array index.

Consider following query where firstSkill will gives array element
at index 0 in skills array ,while lastSkill gives last element in
skills array.

db.candidate.aggregate({
    $project: {
        name: 1,
        email:1,
        age: 1,
        skills: 1,
        firstSkill:{$arrayElemAt:["$skills",0]},
        lastSkill:{$arrayElemAt:["$skills",-1]}
    }}
)

Output:
[
  {
    _id: ObjectId('66261a2330f542d208ef634c'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: [ 'Java', 'MongoDB', 'Node', 'Angular' ],
    firstSkill: 'Java',
    lastSkill: 'Angular'
  }
]


$unwind:
Deconstructs an array field from the input documents to output a document for
each element. Each output document is the input document with the value of the
array field replaced by the element.

db.candidate.aggregate([{
    $project: {
        name: 1,
        email:1,
        age: 1,
        skills: 1,
    }},
    { $unwind: "$skills" }
])

Output:
[
  {
    _id: ObjectId('6626147f30f542d208ef634b'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: 'Java'
  },
  {
    _id: ObjectId('6626147f30f542d208ef634b'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: 'MongoDB'
  },
  {
    _id: ObjectId('6626147f30f542d208ef634b'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: 'Node'
  },
  {
    _id: ObjectId('6626147f30f542d208ef634b'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: 'Angular'
  }
]

We can unwind without project operator

db.candidate.aggregate([
    { $unwind: "$skills" }
])
 
 or

db.candidate.aggregate([
    {$unwind : {path: "$skills" }}
])

Output of these two queries is same as previous one.

Get Array Index with unwind:

db.candidate.aggregate([
    {$unwind : {path: "$skills", includeArrayIndex: "skillIndex" ,preserveNullAndEmptyArrays: true }}
])

Output:
[
  {
    _id: ObjectId('6626147f30f542d208ef634b'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    company: 'Techy Hunger',
    skills: 'Java',
    skillIndex: Long('0')
  },
  {
    _id: ObjectId('6626147f30f542d208ef634b'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    company: 'Techy Hunger',
    skills: 'MongoDB',
    skillIndex: Long('1')
  },
  {
    _id: ObjectId('6626147f30f542d208ef634b'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    company: 'Techy Hunger',
    skills: 'Node',
    skillIndex: Long('2')
  },
  {
    _id: ObjectId('6626147f30f542d208ef634b'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    company: 'Techy Hunger',
    skills: 'Angular',
    skillIndex: Long('3')
  }
]

Here we observe that skillIndex is basically index of array element.

We can also unwind an object array.

Lets have onemore collection "salesData"

//drop if collection pre exists
db.salesData.drop()

Now add some sample data

db.salesData.insertMany( [
   {
      _id: 0,
      items: [
         { item_id: 43, quantity: 2, price: 10 },
         { item_id: 2, quantity: 1, price: 240 }
      ]
   },
   {
      _id: 1,
      items: [
         { item_id: 23, quantity: 3, price: 110 },
         { item_id: 103, quantity: 4, price: 5 },
         { item_id: 38, quantity: 1, price: 300 }
      ]
   },
   {
      _id: 2,
      items: [
         { item_id: 4, quantity: 1, price: 23 }
      ]
   }
] )

Lets unwind object array items.

db.salesData.aggregate([
    { $unwind: "$items" }
])

Output:
[
  { _id: 0, items: { item_id: 43, quantity: 2, price: 10 } },
  { _id: 0, items: { item_id: 2, quantity: 1, price: 240 } },
  { _id: 1, items: { item_id: 23, quantity: 3, price: 110 } },
  { _id: 1, items: { item_id: 103, quantity: 4, price: 5 } },
  { _id: 1, items: { item_id: 38, quantity: 1, price: 300 } },
  { _id: 2, items: { item_id: 4, quantity: 1, price: 23 } }
]

$filter:
Selects a subset of an array to return based on the specified condition. Returns
an array with only those elements that match the condition. The returned
elements are in the original order.


db.salesData.aggregate([
    {
      $project: {
         items: {
            $filter: {
               input: "$items",
               as: "item",
               cond: { $gte: [ "$$item.price", 100 ] }
            }
         }
      }
   }
])
This query will filter out elements of items array where price is greater than 100. 

Output:
[
  { _id: 0, items: [ { item_id: 2, quantity: 1, price: 240 } ] },
  {
    _id: 1,
    items: [
      { item_id: 23, quantity: 3, price: 110 },
      { item_id: 38, quantity: 1, price: 300 }
    ]
  },
  { _id: 2, items: [] }
]

Combining unwind with filter:

db.salesData.aggregate([
    {
      $project: {
         items: {
            $filter: {
               input: "$items",
               as: "item",
               cond: { $gte: [ "$$item.price", 100 ] }
            }
         }
      }
   },
    { $unwind: "$items" }
])

This query is almost same as previous query just we added additional 
unwind on items array of previous result.

Output:
[
  { _id: 0, items: { item_id: 2, quantity: 1, price: 240 } },
  { _id: 1, items: { item_id: 23, quantity: 3, price: 110 } },
  { _id: 1, items: { item_id: 38, quantity: 1, price: 300 } }
]

Convert object to key value pair:

Here we will filter out only customer experience with 
company "Back Gemini".

db.candidate.aggregate([
    {
       $project:{
            name: 1,
            email:1,
            age: 1,
            skills: 1,
            company:{
                $filter:{
                    input:"$company",
                    as :"cmpn",
                    cond:{$eq:["$$cmpn.name","Back Gemini"]}
                }
            }
        }
    },
    {
        $unwind:"$company"
    },
    {
        $project:{
            name: 1,
            email:1,
            age: 1,
            skills: 1,
           "companyName":"$company.name",
           "DOJ":"$company.from",
           "DOS":"$company.to"
        }
    }
])

Output:
[
  {
    _id: ObjectId('6626273030f542d208ef6351'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: [ 'Java', 'MongoDB', 'Node', 'Angular' ],
    companyName: 'Back Gemini',
    DOJ: ISODate('2022-08-20T00:00:00.000Z'),
    DOS: ISODate('2023-05-31T00:00:00.000Z')
  }
]



If you don't want to filter out any company experience
then here is simple query.

db.candidate.aggregate([
    {
        $unwind:"$company"
    },
    {
        $project:{
            name: 1,
            email:1,
            age: 1,
            skills: 1,
           "companyName":"$company.name",
           "DOJ":"$company.from",
           "DOS":"$company.to"
        }
    }
])

Output:
[
  {
    _id: ObjectId('6626273030f542d208ef6351'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: [ 'Java', 'MongoDB', 'Node', 'Angular' ],
    companyName: 'Techy Hunger',
    DOJ: ISODate('2023-06-10T00:00:00.000Z'),
    DOS: ISODate('2024-01-01T00:00:00.000Z')
  },
  {
    _id: ObjectId('6626273030f542d208ef6351'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: [ 'Java', 'MongoDB', 'Node', 'Angular' ],
    companyName: 'Back Gemini',
    DOJ: ISODate('2022-08-20T00:00:00.000Z'),
    DOS: ISODate('2023-05-31T00:00:00.000Z')
  },
  {
    _id: ObjectId('6626273030f542d208ef6351'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: [ 'Java', 'MongoDB', 'Node', 'Angular' ],
    companyName: 'ECS',
    DOJ: ISODate('2022-01-01T00:00:00.000Z'),
    DOS: ISODate('2022-07-31T00:00:00.000Z')
  }
]


Suppose we want to know on perticular date range where candidate was working.

Lets us build that query 

db.candidate.aggregate([
    {
       $project:{
            name: 1,
            email:1,
            age: 1,
            skills: 1,
            company:{
                $filter:{
                    input:"$company",
                    as :"cmpn",
                    cond:{
                            $and:[
                                { $gte: ["$$cmpn.from",ISODate("2022-01-01")] },
                                { $lte: ["$$cmpn.from",ISODate("2023-01-01")] }
                            ]
                            
                        }
                }
            }
        }
    }
])

Output:
[
  {
    _id: ObjectId('6626273030f542d208ef6351'),
    name: 'Avinash',
    email: 'avinash@example.com',
    age: 27,
    skills: [ 'Java', 'MongoDB', 'Node', 'Angular' ],
    company: [
      {
        name: 'Back Gemini',
        from: ISODate('2022-08-20T00:00:00.000Z'),
        to: ISODate('2023-05-31T00:00:00.000Z')
      },
      {
        name: 'ECS',
        from: ISODate('2022-01-01T00:00:00.000Z'),
        to: ISODate('2022-07-31T00:00:00.000Z')
      }
    ]
  }
]