Conditionally select fields from an array in MongoDB document











up vote
1
down vote

favorite












I am relatively new to mongodb. I have a document like:



{
"_id" : ObjectId("5bcf50938847292ecbadc3c1"),
"obsrvblKy" : "ABCDEFGHIJ",
"obsrvblKnd" : "PRICE",
"pblshrNm" : "BT",
"pblshrSrc" : "BT",
"dstrbtr" : "BT",
"crtdOn" : ISODate("2018-10-23T12:47:15.544Z"),
"qut" : [
{
"qlfr" : "BID",
"vl" : 100,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
},
{
"qlfr" : "ASK",
"vl" : 101,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
},
{
"qlfr" : "MID",
"vl" : 100.50,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
}
],
"mrkCurrncy" : "USD",
"sttlmntDt" : "2018-10-30"
}


I want this document to be transformed to a more simpler version enabling it to be downloaded into a CSV format.



{
"obsrvblKy" : "ABCDEFGHIJ",
"obsrvblKnd" : "PRICE",
"pblshrNm" : "BT",
"pblshrSrc" : "BT",
"dstrbtr" : "BT",
"Bid": 100,
"Bid-Timestamp": ISODate("2018-10-23T12:47:05.524Z"),
"Ask": 101,
"Ask-Timestamp": ISODate("2018-10-23T12:47:05.524Z"),
"Mid": 100.50,
"Mid-Timestamp": ISODate("2018-10-23T12:47:05.524Z")
}


Can someone point me to how this can be done.










share|improve this question






















  • what hve you tried so far ?
    – 0.sh
    Nov 11 at 15:28















up vote
1
down vote

favorite












I am relatively new to mongodb. I have a document like:



{
"_id" : ObjectId("5bcf50938847292ecbadc3c1"),
"obsrvblKy" : "ABCDEFGHIJ",
"obsrvblKnd" : "PRICE",
"pblshrNm" : "BT",
"pblshrSrc" : "BT",
"dstrbtr" : "BT",
"crtdOn" : ISODate("2018-10-23T12:47:15.544Z"),
"qut" : [
{
"qlfr" : "BID",
"vl" : 100,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
},
{
"qlfr" : "ASK",
"vl" : 101,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
},
{
"qlfr" : "MID",
"vl" : 100.50,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
}
],
"mrkCurrncy" : "USD",
"sttlmntDt" : "2018-10-30"
}


I want this document to be transformed to a more simpler version enabling it to be downloaded into a CSV format.



{
"obsrvblKy" : "ABCDEFGHIJ",
"obsrvblKnd" : "PRICE",
"pblshrNm" : "BT",
"pblshrSrc" : "BT",
"dstrbtr" : "BT",
"Bid": 100,
"Bid-Timestamp": ISODate("2018-10-23T12:47:05.524Z"),
"Ask": 101,
"Ask-Timestamp": ISODate("2018-10-23T12:47:05.524Z"),
"Mid": 100.50,
"Mid-Timestamp": ISODate("2018-10-23T12:47:05.524Z")
}


Can someone point me to how this can be done.










share|improve this question






















  • what hve you tried so far ?
    – 0.sh
    Nov 11 at 15:28













up vote
1
down vote

favorite









up vote
1
down vote

favorite











I am relatively new to mongodb. I have a document like:



{
"_id" : ObjectId("5bcf50938847292ecbadc3c1"),
"obsrvblKy" : "ABCDEFGHIJ",
"obsrvblKnd" : "PRICE",
"pblshrNm" : "BT",
"pblshrSrc" : "BT",
"dstrbtr" : "BT",
"crtdOn" : ISODate("2018-10-23T12:47:15.544Z"),
"qut" : [
{
"qlfr" : "BID",
"vl" : 100,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
},
{
"qlfr" : "ASK",
"vl" : 101,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
},
{
"qlfr" : "MID",
"vl" : 100.50,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
}
],
"mrkCurrncy" : "USD",
"sttlmntDt" : "2018-10-30"
}


I want this document to be transformed to a more simpler version enabling it to be downloaded into a CSV format.



{
"obsrvblKy" : "ABCDEFGHIJ",
"obsrvblKnd" : "PRICE",
"pblshrNm" : "BT",
"pblshrSrc" : "BT",
"dstrbtr" : "BT",
"Bid": 100,
"Bid-Timestamp": ISODate("2018-10-23T12:47:05.524Z"),
"Ask": 101,
"Ask-Timestamp": ISODate("2018-10-23T12:47:05.524Z"),
"Mid": 100.50,
"Mid-Timestamp": ISODate("2018-10-23T12:47:05.524Z")
}


Can someone point me to how this can be done.










share|improve this question













I am relatively new to mongodb. I have a document like:



{
"_id" : ObjectId("5bcf50938847292ecbadc3c1"),
"obsrvblKy" : "ABCDEFGHIJ",
"obsrvblKnd" : "PRICE",
"pblshrNm" : "BT",
"pblshrSrc" : "BT",
"dstrbtr" : "BT",
"crtdOn" : ISODate("2018-10-23T12:47:15.544Z"),
"qut" : [
{
"qlfr" : "BID",
"vl" : 100,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
},
{
"qlfr" : "ASK",
"vl" : 101,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
},
{
"qlfr" : "MID",
"vl" : 100.50,
"mrkTmstmp" : ISODate("2018-10-23T12:47:05.524Z"),
"mrkDt" : ISODate("2018-10-23T00:00:00Z")
}
],
"mrkCurrncy" : "USD",
"sttlmntDt" : "2018-10-30"
}


I want this document to be transformed to a more simpler version enabling it to be downloaded into a CSV format.



{
"obsrvblKy" : "ABCDEFGHIJ",
"obsrvblKnd" : "PRICE",
"pblshrNm" : "BT",
"pblshrSrc" : "BT",
"dstrbtr" : "BT",
"Bid": 100,
"Bid-Timestamp": ISODate("2018-10-23T12:47:05.524Z"),
"Ask": 101,
"Ask-Timestamp": ISODate("2018-10-23T12:47:05.524Z"),
"Mid": 100.50,
"Mid-Timestamp": ISODate("2018-10-23T12:47:05.524Z")
}


Can someone point me to how this can be done.







mongodb mongodb-query aggregation-framework






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 11 at 14:56









Senthil Vaithilingam

91




91












  • what hve you tried so far ?
    – 0.sh
    Nov 11 at 15:28


















  • what hve you tried so far ?
    – 0.sh
    Nov 11 at 15:28
















what hve you tried so far ?
– 0.sh
Nov 11 at 15:28




what hve you tried so far ?
– 0.sh
Nov 11 at 15:28












2 Answers
2






active

oldest

votes

















up vote
0
down vote













You can use below aggregation



db.collection.aggregate([
{ "$replaceRoot": {
"newRoot": {
"$mergeObjects": [
"$$ROOT",
{ "$arrayToObject": {
"$reduce": {
"input": "$qut",
"initialValue": ,
"in": {
"$concatArrays": [
[
{ "k": "$$this.qlfr", "v": "$$this.vl" },
{ "k": { "$concat": ["$$this.qlfr", "-", "TimeStamp"] }, "v": "$$this.mrkTmstmp" }
],
"$$value"
]
}
}
}}
]
}
}},
{ "$project": { "qut": 0 }}
])


Output



[
{
"ASK": 101,
"ASK-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
"BID": 100,
"BID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
"MID": 100.5,
"MID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
"_id": ObjectId("5bcf50938847292ecbadc3c1"),
"crtdOn": ISODate("2018-10-23T12:47:15.544Z"),
"dstrbtr": "BT",
"mrkCurrncy": "USD",
"obsrvblKnd": "PRICE",
"obsrvblKy": "ABCDEFGHIJ",
"pblshrNm": "BT",
"pblshrSrc": "BT",
"sttlmntDt": "2018-10-30"
}
]





share|improve this answer






























    up vote
    0
    down vote













    You can below aggregation in 3.6 version.



    db.colname.aggregate([
    {"$replaceRoot":{
    "newRoot":{
    "$reduce":{
    "input":"$qut",
    "initialValue":"$$ROOT",
    "in":{
    "$mergeObjects":[
    {"$arrayToObject":[[
    ["$$this.qlfr","$$this.vl"],
    [{"$concat":["$$this.qlfr","-","TimeStamp"]},"$$this.mrkTmstmp"]
    ]]},
    "$$value"
    ]
    }
    }
    }
    }},
    {"$project":{"crtdOn":0,"qut":0,"mrkCurrncy":0,"sttlmntDt":0}}
    ])





    share|improve this answer























    • Did the answer work for you ?
      – Veeram
      Nov 13 at 12:28











    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53249945%2fconditionally-select-fields-from-an-array-in-mongodb-document%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    0
    down vote













    You can use below aggregation



    db.collection.aggregate([
    { "$replaceRoot": {
    "newRoot": {
    "$mergeObjects": [
    "$$ROOT",
    { "$arrayToObject": {
    "$reduce": {
    "input": "$qut",
    "initialValue": ,
    "in": {
    "$concatArrays": [
    [
    { "k": "$$this.qlfr", "v": "$$this.vl" },
    { "k": { "$concat": ["$$this.qlfr", "-", "TimeStamp"] }, "v": "$$this.mrkTmstmp" }
    ],
    "$$value"
    ]
    }
    }
    }}
    ]
    }
    }},
    { "$project": { "qut": 0 }}
    ])


    Output



    [
    {
    "ASK": 101,
    "ASK-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
    "BID": 100,
    "BID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
    "MID": 100.5,
    "MID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
    "_id": ObjectId("5bcf50938847292ecbadc3c1"),
    "crtdOn": ISODate("2018-10-23T12:47:15.544Z"),
    "dstrbtr": "BT",
    "mrkCurrncy": "USD",
    "obsrvblKnd": "PRICE",
    "obsrvblKy": "ABCDEFGHIJ",
    "pblshrNm": "BT",
    "pblshrSrc": "BT",
    "sttlmntDt": "2018-10-30"
    }
    ]





    share|improve this answer



























      up vote
      0
      down vote













      You can use below aggregation



      db.collection.aggregate([
      { "$replaceRoot": {
      "newRoot": {
      "$mergeObjects": [
      "$$ROOT",
      { "$arrayToObject": {
      "$reduce": {
      "input": "$qut",
      "initialValue": ,
      "in": {
      "$concatArrays": [
      [
      { "k": "$$this.qlfr", "v": "$$this.vl" },
      { "k": { "$concat": ["$$this.qlfr", "-", "TimeStamp"] }, "v": "$$this.mrkTmstmp" }
      ],
      "$$value"
      ]
      }
      }
      }}
      ]
      }
      }},
      { "$project": { "qut": 0 }}
      ])


      Output



      [
      {
      "ASK": 101,
      "ASK-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
      "BID": 100,
      "BID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
      "MID": 100.5,
      "MID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
      "_id": ObjectId("5bcf50938847292ecbadc3c1"),
      "crtdOn": ISODate("2018-10-23T12:47:15.544Z"),
      "dstrbtr": "BT",
      "mrkCurrncy": "USD",
      "obsrvblKnd": "PRICE",
      "obsrvblKy": "ABCDEFGHIJ",
      "pblshrNm": "BT",
      "pblshrSrc": "BT",
      "sttlmntDt": "2018-10-30"
      }
      ]





      share|improve this answer

























        up vote
        0
        down vote










        up vote
        0
        down vote









        You can use below aggregation



        db.collection.aggregate([
        { "$replaceRoot": {
        "newRoot": {
        "$mergeObjects": [
        "$$ROOT",
        { "$arrayToObject": {
        "$reduce": {
        "input": "$qut",
        "initialValue": ,
        "in": {
        "$concatArrays": [
        [
        { "k": "$$this.qlfr", "v": "$$this.vl" },
        { "k": { "$concat": ["$$this.qlfr", "-", "TimeStamp"] }, "v": "$$this.mrkTmstmp" }
        ],
        "$$value"
        ]
        }
        }
        }}
        ]
        }
        }},
        { "$project": { "qut": 0 }}
        ])


        Output



        [
        {
        "ASK": 101,
        "ASK-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
        "BID": 100,
        "BID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
        "MID": 100.5,
        "MID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
        "_id": ObjectId("5bcf50938847292ecbadc3c1"),
        "crtdOn": ISODate("2018-10-23T12:47:15.544Z"),
        "dstrbtr": "BT",
        "mrkCurrncy": "USD",
        "obsrvblKnd": "PRICE",
        "obsrvblKy": "ABCDEFGHIJ",
        "pblshrNm": "BT",
        "pblshrSrc": "BT",
        "sttlmntDt": "2018-10-30"
        }
        ]





        share|improve this answer














        You can use below aggregation



        db.collection.aggregate([
        { "$replaceRoot": {
        "newRoot": {
        "$mergeObjects": [
        "$$ROOT",
        { "$arrayToObject": {
        "$reduce": {
        "input": "$qut",
        "initialValue": ,
        "in": {
        "$concatArrays": [
        [
        { "k": "$$this.qlfr", "v": "$$this.vl" },
        { "k": { "$concat": ["$$this.qlfr", "-", "TimeStamp"] }, "v": "$$this.mrkTmstmp" }
        ],
        "$$value"
        ]
        }
        }
        }}
        ]
        }
        }},
        { "$project": { "qut": 0 }}
        ])


        Output



        [
        {
        "ASK": 101,
        "ASK-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
        "BID": 100,
        "BID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
        "MID": 100.5,
        "MID-TimeStamp": ISODate("2018-10-23T12:47:05.524Z"),
        "_id": ObjectId("5bcf50938847292ecbadc3c1"),
        "crtdOn": ISODate("2018-10-23T12:47:15.544Z"),
        "dstrbtr": "BT",
        "mrkCurrncy": "USD",
        "obsrvblKnd": "PRICE",
        "obsrvblKy": "ABCDEFGHIJ",
        "pblshrNm": "BT",
        "pblshrSrc": "BT",
        "sttlmntDt": "2018-10-30"
        }
        ]






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 11 at 16:46

























        answered Nov 11 at 16:40









        Anthony Winzlet

        12.3k41038




        12.3k41038
























            up vote
            0
            down vote













            You can below aggregation in 3.6 version.



            db.colname.aggregate([
            {"$replaceRoot":{
            "newRoot":{
            "$reduce":{
            "input":"$qut",
            "initialValue":"$$ROOT",
            "in":{
            "$mergeObjects":[
            {"$arrayToObject":[[
            ["$$this.qlfr","$$this.vl"],
            [{"$concat":["$$this.qlfr","-","TimeStamp"]},"$$this.mrkTmstmp"]
            ]]},
            "$$value"
            ]
            }
            }
            }
            }},
            {"$project":{"crtdOn":0,"qut":0,"mrkCurrncy":0,"sttlmntDt":0}}
            ])





            share|improve this answer























            • Did the answer work for you ?
              – Veeram
              Nov 13 at 12:28















            up vote
            0
            down vote













            You can below aggregation in 3.6 version.



            db.colname.aggregate([
            {"$replaceRoot":{
            "newRoot":{
            "$reduce":{
            "input":"$qut",
            "initialValue":"$$ROOT",
            "in":{
            "$mergeObjects":[
            {"$arrayToObject":[[
            ["$$this.qlfr","$$this.vl"],
            [{"$concat":["$$this.qlfr","-","TimeStamp"]},"$$this.mrkTmstmp"]
            ]]},
            "$$value"
            ]
            }
            }
            }
            }},
            {"$project":{"crtdOn":0,"qut":0,"mrkCurrncy":0,"sttlmntDt":0}}
            ])





            share|improve this answer























            • Did the answer work for you ?
              – Veeram
              Nov 13 at 12:28













            up vote
            0
            down vote










            up vote
            0
            down vote









            You can below aggregation in 3.6 version.



            db.colname.aggregate([
            {"$replaceRoot":{
            "newRoot":{
            "$reduce":{
            "input":"$qut",
            "initialValue":"$$ROOT",
            "in":{
            "$mergeObjects":[
            {"$arrayToObject":[[
            ["$$this.qlfr","$$this.vl"],
            [{"$concat":["$$this.qlfr","-","TimeStamp"]},"$$this.mrkTmstmp"]
            ]]},
            "$$value"
            ]
            }
            }
            }
            }},
            {"$project":{"crtdOn":0,"qut":0,"mrkCurrncy":0,"sttlmntDt":0}}
            ])





            share|improve this answer














            You can below aggregation in 3.6 version.



            db.colname.aggregate([
            {"$replaceRoot":{
            "newRoot":{
            "$reduce":{
            "input":"$qut",
            "initialValue":"$$ROOT",
            "in":{
            "$mergeObjects":[
            {"$arrayToObject":[[
            ["$$this.qlfr","$$this.vl"],
            [{"$concat":["$$this.qlfr","-","TimeStamp"]},"$$this.mrkTmstmp"]
            ]]},
            "$$value"
            ]
            }
            }
            }
            }},
            {"$project":{"crtdOn":0,"qut":0,"mrkCurrncy":0,"sttlmntDt":0}}
            ])






            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Nov 11 at 17:25

























            answered Nov 11 at 15:44









            Veeram

            37.5k33057




            37.5k33057












            • Did the answer work for you ?
              – Veeram
              Nov 13 at 12:28


















            • Did the answer work for you ?
              – Veeram
              Nov 13 at 12:28
















            Did the answer work for you ?
            – Veeram
            Nov 13 at 12:28




            Did the answer work for you ?
            – Veeram
            Nov 13 at 12:28


















            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53249945%2fconditionally-select-fields-from-an-array-in-mongodb-document%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Full-time equivalent

            さくらももこ

            13 indicted, 8 arrested in Calif. drug cartel investigation