transform data using mongodb aggregation query

shettysa...@gmail.com

unread,

Sep 12, 2018, 9:57:14 PM9/12/18

to mongodb-user

Hi

I am using mongdb 3.6 on linux server.

i have the following collection in my db

{

"_id" : "000023",

"createdTimeStamp" : ISODate("2018-09-12T15:57:57.853Z"),

"priceHierarchies" : [

{

"level" : "PLT",

"phid" : "21",

"priceUnit" : [

{

"active" : true,

"unitOfPrice" : "KG",

"priceByCurrency" : [

{

"currency" : "GBP",

"mergedPriceInfoList" : [

{

"validFrom" : ISODate("2018-06-04T23:00:00.000Z"),

"validTo" : ISODate("2018-09-13T23:00:00.000Z"),

"unitQty" : 0,

"amount" : "10090",

}

]

},

{

"currency" : "EUR",

"mergedPriceInfoList" : [

{

"validFrom" : ISODate("2018-06-04T23:00:00.000Z"),

"validTo" : ISODate("2018-09-13T23:00:00.000Z"),

= "unitQty" : 1000,

"amount" : "2121",

}

]

}

]

}

]

},

{

"level" : "STE",

"phid" : "5526",

"priceUnit" : [

{

"active" : true,

"unitOfPrice" : "KG",

"priceByCurrency" : [

{

"currency" : "GBP",

"mergedPriceInfoList" : [

{

"validFrom" : ISODate("2018-06-04T23:00:00.000Z"),

"validTo" : ISODate("2018-09-13T23:00:00.000Z"),

"unitQty" : 0,

"amount" : "10090",

}

]

}

]

}

]

}

]

}

i want to convert the above collection to the below data structure when the user does a find on the phid's i.e 21,5526

{

"productId": "000023",

"priceHierarchies": [{

"level": "PLT",

"phid": "21",

"priceByCurrency": [{

"currency": "GBP",

"priceInfoList": [{

"unitOfPrice": "KG",

"unitQty": 0,

"amount": "10090",

"validFrom": "2018-06-04",

"validTo": "2018-09-13"

}]

},

{

"currency": "EUR",

"priceInfoList": [{

"unitOfPrice": "KG",

"unitQty": 1000,

"amount": "2121",

"validFrom": "2018-06-04",

"validTo": "2018-09-13"

}]

}

]

},

{

"level": "STE",

"phid": "5526",

"priceByCurrency": [{

"currency": "GBP",

"priceInfoList": [{

"unitOfPrice": "KG",

"unitQty": 0,

"amount": "10090",

"validFrom": "2018-06-04",

"validTo": "2018-09-13"

}]

}

]

}

i tried to use aggregation queryies but unfortunately it is giving me duplicate data in priceByCurrency array

can someone please help me out

Robert Cochran

unread,

Sep 13, 2018, 7:46:04 PM9/13/18

to mongodb-user

Hi!

Can you please explain more regarding what you want. I don't understand your request, and need some additional explanation. Here is what I seem to understand.

You are saying:

1. A user -- any user -- might query collection_a with a db.collection.find({}) query, coded like this and done in the Mongo shell:

db.collectiona.find( { "_id" : "000023" } )

Is the above correct? If not, how would the connected user be making the query, and what exactly will be coded in the db.collectiona.find()? Will the query be made using node.js, for example? If so, what version of node, exactly, and what version of the mongoldb driver for node.js are we talking about?

To continue. The user makes the above request, and what you want to do is: instead of showing the document with _id equal to "000023" in collectiona, you want to substitute some other document from a different collection?

Or do you want to transform whatever is returned for _id = "000023" in collectiona into a different output document?

If you really want _id = "000023" transformed into a completely new output document, how exactly should this transformation or reformatted document be created? It helps a lot to give step by step instructions, rather than just presenting a plain document with no commentary on how to build it.

I also do not really understand why you want to present the user with a "transformed" document instead of the original document that he or she is querying for, as a result of the db.collectiona.find({}). That point confuses me.

Also, what operating system would the MongoDB server be running on? You say you are using MongoDB server version 3.6. Can you tell me the full server version? I have 3.6.7 on my own machine. There have been a lot of fixes to the 3.6 series since 3.6.0 was released.

Do you have sample documents that illustrate what you want? If possible, could you provide small *.json files that show the "input" and "output" documents?

I can't promise to deliver what you want...but I know you have posted about this at least once before. Perhaps I can help, perhaps not. Perhaps with additional explanation of what you have in mind, it will be easier for other readers to help you, too.

Thanks so much

Bob

sachin shetty

unread,

Sep 14, 2018, 9:17:48 AM9/14/18

to mongod...@googlegroups.com

hi Bob

to give the background .. we have built an core application which run the query passed to it by different calling microservices and uploads it to the location in cloud. we have a endpoint defined in the microservices. on the basis of the end point we generate the query and passing it to the core application to execute the query and upload it to the cloud.]

so microservice A -> /api/v1/price?nodeid=xxxx -> generate the query - > pass it to the core app -> coreapp executes the query-> coreapp upload the result on cloud in json format

normally it should have been a simple find/aggregate match query with little bit of projection. but the problem is our database schema does not match with our endpoint schema . hence all the trouble to convert the collection from one strucuture to another to match the schema.

we use mongodb 3.6.7 on mongodb atlas.

a simple mongodb find query wont help as i need to project my query to return data as per the endpoint schema.

so my query would be somehting like this :

db.getCollection('Test_10').aggregate(

{'$match':{'priceHierarchies.phid':{ '$in':['526','121']}}},

{'$unwind':'$priceHierarchies'},

{'$match':{'priceHierarchies.phid':{ '$in':['526','121']}}},

there is more this query but basically this would be the query to get the data for the matching nodeids (21 and 5526)

rest of the query would be dedicate to display the data as per the endpint scheme.

i am attaching the input and output json and the aggregation query which i am using.

hope i am able to clarify some of your question . please let me know if more information is needed.

Regards

Sachin K. Shetty

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/c04cc08c-9005-4ecf-849a-1cbdfebbfa3f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

aggregation_query.txt

inputjson.json

outputjson.json

Robert Cochran

unread,

Sep 14, 2018, 6:49:17 PM9/14/18

to mongodb-user

Hi Sachin!

Thank you for the explanation. I will study it, and also study the data you so kindly provided. I really appreciate this.

I probably do not have the required skill to give you the output you want. I am currently taking a class which will prove helpful, but that has much homework associated with it and I'll need to focus on it for a few weeks. I just want to be honest about this. You will probably not see anything from me in a short time frame. Over several weeks of time, I will say more but may not be able to produce what you need.