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')
}
]
}
]
No comments:
Post a Comment