I have a collection like this:
{
'_id' : ObjectId('6251f8556e75125f9260f333'),
'name': 'jojo',
'profile': 'jojo profile',
'date': ISODate("2022-04-09T21:18:40.473Z"),
'look': [
{ 'art': 'group-id', 'data': 'alma', 'dt': '1'},
{ 'art': 'called', 'data': 'central', 'dt': '1'},
{ 'art': 'access-time', 'data': 108000, 'dt': '1'}
]
'answer': [
{ 'art': 'rate-id', 'data': 'limit1', 'dt': '1'},
{ 'art': 'protocol', 'data': 'tcp', 'dt': '1'}
]
},
{
'_id' : ObjectId('6251f8306e75125f9260f332'),
'name': 'dodo',
'profile': 'dodo profile',
'date': ISODate("2022-04-09T15:20:58.562Z"),
'look': [
{ 'art': 'group-id', 'data': 'alma', 'dt': '1'},
{ 'art': 'called', 'data': 'central', 'dt': '1'},
]
'answer': [
{ 'art': 'rate-id', 'data': 'limit1', 'dt': '1'},
]
},
{
'_id' : ObjectId('6251a5113700ba4a0a59c48f'),
'name': 'kaka',
'profile': 'kaka profile',
'date': ISODate("2022-04-09T15:22:25.816Z"),
'look': [
{ 'art': 'access-time', 'data': 50400, 'dt': '1'}
]
'answer': [
{ 'art': 'protocol', 'data': 'tcp', 'dt': '1'}
]
}
and I was expecting an output like this:
{
'_id' : ObjectId('6251f8556e75125f9260f333'),
'name': 'jojo',
'profile': 'jojo profile',
'date': ISODate("2022-04-09T21:18:40.473Z"),
'goup': 'alma', // filter by 'group-id' and put value of data field
'called': 'central', // filter by 'called' and put value of data field
'accessTime': 108000, // filter by 'access-time' and put value of data field
'rate': 'limi1', // filter by 'rate-id' and put value of data field
'protocol': 'tcp', // filter by 'protocol' and put value of data field
},
{
'_id' : ObjectId('6251f8306e75125f9260f332'),
'name': 'dodo',
'profile': 'dodo profile',
'date': ISODate("2022-04-09T15:20:58.562Z"),
'goup': 'alma',
'called': 'central',
'accessTime': '', // set blank data if not exist
'rate': 'limi1',
'protocol': '', // set blank data if not exist
},
{
'_id' : ObjectId('6251a5113700ba4a0a59c48f'),
'name': 'kaka',
'profile': 'kaka profile',
'date': ISODate("2022-04-09T15:22:25.816Z"),
'goup': '', // set blank data if not exist
'called': '', // set blank data if not exist
'accessTime': 50400,
'rate': '', // set blank data if not exist
'protocol': 'tcp',
}
I've searched here but couldn't find an answer that matches the problem I'm facing, probably because of the wrong keywords. Since I'm new to mongodb, I'm confused about how to solve the query I want. How can I achieve this? Please help me...
CodePudding user response：
For this you should use the aggregation framework of mongo db, because will require complex operations to get the data in the shape that you want.
https://www.mongodb.com/docs/manual/aggregation/
Every aggregation is an array of stages and every stage does something specific.
I used the next stages:
- addFields: Allows you to add new fields to the response of every document, so if you don't have group in the document, that will add or replace it.
- project: Allows you remove some fields of a document. In a projection stage if you set an attribute as 0 that will remove that attribute from the response.
Also I used some operators:
- filter: this allows you to filter data of an element that is an array
- arrayElemenAt: receives an array and return the position specified
The pipeline:
[
{
"$addFields":{
"group":{
"$arrayElemAt":[
{
"$filter":{
"input":"$look",
"as":"item",
"cond":{
"$eq":[
"$$item.art",
"group-id"
]
}
}
},
0
]
},
"called":{
"$arrayElemAt":[
{
"$filter":{
"input":"$look",
"as":"item",
"cond":{
"$eq":[
"$$item.art",
"called"
]
}
}
},
0
]
},
"accessTime":{
"$arrayElemAt":[
{
"$filter":{
"input":"$look",
"as":"item",
"cond":{
"$eq":[
"$$item.art",
"access-time"
]
}
}
},
0
]
},
"rate":{
"$arrayElemAt":[
{
"$filter":{
"input":"$answer",
"as":"item",
"cond":{
"$eq":[
"$$item.art",
"rate-id"
]
}
}
},
0
]
},
"protocol":{
"$arrayElemAt":[
{
"$filter":{
"input":"$answer",
"as":"item",
"cond":{
"$eq":[
"$$item.art",
"protocol"
]
}
}
},
0
]
}
}
},
{
"$addFields":{
"group":"$group.data",
"called":"$called.data",
"accessTime":"$accessTime.data",
"rate":"$rate.data",
"protocol":"$protocol.data"
}
},
{
"$project":{
"look":0,
"answer":0
}
}
]
CodePudding user response：
This is quite cumbersome with the current structure, as for each field you have to convert the object to an array, filter it then convert it back, here's how it looks:
db.collection.aggregate([
{
$replaceRoot: {
newRoot: {
"$mergeObjects": [
{
_id: "$_id",
name: "$name",
profile: "$profile",
date: "$date",
},
{
"$arrayToObject": {
$map: {
input: {
$filter: {
input: {
$objectToArray: {
$ifNull: [
{
"$arrayElemAt": [
{
$filter: {
input: {
$ifNull: [
"$look",
[]
]
},
cond: {
$eq: [
"$$this.art",
"group-id"
]
}
}
},
0
]
},
{
art: ""
}
]
}
},
cond: {
$eq: [
"$$this.k",
"data"
]
}
}
},
in: {
k: "goup",
v: "$$this.v"
}
}
}
},
{
"$arrayToObject": {
$map: {
input: {
$filter: {
input: {
$objectToArray: {
$ifNull: [
{
"$arrayElemAt": [
{
$filter: {
input: {
$ifNull: [
"$look",
[]
]
},
cond: {
$eq: [
"$$this.art",
"called"
]
}
}
},
0
]
},
{
art: ""
}
]
}
},
cond: {
$eq: [
"$$this.k",
"data"
]
}
}
},
in: {
k: "called",
v: "$$this.v"
}
}
}
},
{
"$arrayToObject": {
$map: {
input: {
$filter: {
input: {
$objectToArray: {
$ifNull: [
{
"$arrayElemAt": [
{
$filter: {
input: {
$ifNull: [
"$look",
[]
]
},
cond: {
$eq: [
"$$this.art",
"access-time"
]
}
}
},
0
]
},
{
art: ""
}
]
}
},
cond: {
$eq: [
"$$this.k",
"data"
]
}
}
},
in: {
k: "access-time",
v: "$$this.v"
}
}
}
},
{
"$arrayToObject": {
$map: {
input: {
$filter: {
input: {
$objectToArray: {
$ifNull: [
{
"$arrayElemAt": [
{
$filter: {
input: {
$ifNull: [
"$answer",
[]
]
},
cond: {
$eq: [
"$$this.art",
"rate-id"
]
}
}
},
0
]
},
{
art: ""
}
]
}
},
cond: {
$eq: [
"$$this.k",
"data"
]
}
}
},
in: {
k: "rate",
v: "$$this.v"
}
}
}
},
{
"$arrayToObject": {
$map: {
input: {
$filter: {
input: {
$objectToArray: {
$ifNull: [
{
"$arrayElemAt": [
{
$filter: {
input: {
$ifNull: [
"$answer",
[]
]
},
cond: {
$eq: [
"$$this.art",
"protocol"
]
}
}
},
0
]
},
{
art: ""
}
]
}
},
cond: {
$eq: [
"$$this.k",
"data"
]
}
}
},
in: {
k: "protocol",
v: "$$this.v"
}
}
}
}
]
}
}
}
])
If you're using Mongo version 5 , then you can use $getField to simplify the syntax a little bit, here's how one field would look like in this syntax:
goup: {
$getField: {
field: 'data',
input: {
'$arrayElemAt': [
{
$filter: {
input: {
$ifNull: [
'$look',
[],
],
},
cond: {
$eq: [
'$$this.art',
'group-id',
],
},
},
},
0,
],
},
},
},
CodePudding user response：
You would require an aggregate operation that has a pipeline with the following key operators and stages:
$map: an operator to transform thelookand answerarraysinto documents with just mappedkandvfields, crucial for obtaining a hash map with the following operator$arrayToObject: this allows the above to be possible i.e. converting an array into a single document$mergeObjects: combine top level fields i.e._id,date,name,profiletogether with the converted documents above$replaceWith: pipeline stage to replace the root document with the specified document from above
Overall, your pipeline should follow:
[
{ $replaceWith: {
$mergeObjects: [
{
_id: '$_id',
date: '$date',
name: '$name',
profile: '$profile',
protocol: “”,
},
{ $arrayToObject: {
$map: {
input: '$look',
in: { k: '$$this.art', v: '$$this.data' }
}
} },
{ $arrayToObject: {
$map: {
input: '$answer',
in: { k: '$$this.art', v: '$$this.data' }
}
} }
]
} }
]