1

So I have been able to get my gte query to work for mongodb. However I have noticed that the query while yes correctly prints out the document. It does not remove/hide the subarray that are not correct.

For example:

{history:[{starttime:1593143279079},{starttime:1593236606706}}

Now I only want the subarray that gte to 1593236606706

However when I run the following query

{"history.starttime":{$gte:1593236606706}}

I get the correct document as yes the document has a gte then 1593236606706 however I also see all the no-relevant subarray like 1593143279079.

Now I understand I could do a foreach check in node or any other program. But surely there is away to remove or hide the non-relevant documents before it returns?

Mongodb return subarray

Here is the sample data

    {
  "_id": "5ef2bbb25b43e19dab7cac9f",
  "UUID": "72296e08-2086-3b25-b66f-09fe14e7a1b9",
  "ipaddress": "101.118.63.108",
  "history": [
    {
      "HID": "3892b3d0-b5c3-11ea-a984-15a58d42facb",
      "station": "DRN1",
      "starttime": 1592966066060,
      "endtime": 1592966101344
    },
    {
      "HID": "4ccfd300-b5c3-11ea-a984-15a58d42facb",
      "station": "PERTHRadio",
      "starttime": 1592966100016,
      "endtime": 1592966101480
    },
    {
      "HID": "4de53aa0-b5c3-11ea-a984-15a58d42facb",
      "station": "PERTHRadio",
      "starttime": 1592966101833
    },
    {
      "HID": "c4b7af20-b607-11ea-b80a-916b111bece1",
      "station": "PERTHRadio",
      "starttime": 1592995506961,
      "endtime": 1592995508535
    },
    {
      "HID": "c5aa4c80-b607-11ea-b80a-916b111bece1",
      "station": "PERTHRadio",
      "starttime": 1592995508551,
      "endtime": 1592997386659
    },
    {
      "HID": "186f7e40-b68f-11ea-9d92-0ff1a766d952",
      "station": "DRN1",
      "starttime": 1593053629476,
      "endtime": 1593053631582
    },
    {
      "HID": "cb9f79a0-b759-11ea-80ed-dbf0ea8c5f4f",
      "station": "PERTHRadio",
      "starttime": 1593140688441,
      "endtime": 1593141273000
    },
    {
      "HID": "e0c4d920-b75d-11ea-80ed-dbf0ea8c5f4f",
      "station": "PERTHRadio",
      "starttime": 1593142441905
    },
    {
      "HID": "92b648d0-b75e-11ea-a743-2994856ad452",
      "station": "PERTHRadio",
      "starttime": 1593142740444
    },
    {
      "HID": "9db86e20-b75e-11ea-a743-2994856ad452",
      "station": "PERTHRadio",
      "starttime": 1593142758914,
      "endtime": 1593142760449
    },
    {
      "HID": "9ed576e0-b75e-11ea-a743-2994856ad452",
      "station": "PERTHRadio",
      "starttime": 1593142760781
    },
    {
      "HID": "d670b1a0-b75e-11ea-a29a-c19990a8e870",
      "station": "PERTHRadio",
      "starttime": 1593142854073
    },
    {
      "HID": "d74e1950-b75e-11ea-a29a-c19990a8e870",
      "station": "PERTHRadio",
      "starttime": 1593142855525,
      "endtime": 1593142860214
    },
    {
      "HID": "d948b0d0-b75e-11ea-a29a-c19990a8e870",
      "station": "PERTHRadio",
      "starttime": 1593142858845,
      "endtime": 1593142860481
    },
    {
      "HID": "da540650-b75e-11ea-a29a-c19990a8e870",
      "station": "PERTHRadio",
      "starttime": 1593142860597
    },
    {
      "HID": "e8f683e0-b75e-11ea-a56b-635bc76b120b",
      "station": "PERTHRadio",
      "starttime": 1593142885149
    },
    {
      "HID": "3bc788d0-b75f-11ea-aab1-4d8374b59f5f",
      "station": "PERTHRadio",
      "starttime": 1593143024093
    },
    {
      "HID": "5b69add0-b75f-11ea-8e0e-9de052ed5763",
      "station": "PERTHRadio",
      "starttime": 1593143077164
    },
    {
      "HID": "79db9030-b75f-11ea-b9e5-6780a994ba91",
      "station": "PERTHRadio",
      "starttime": 1593143128243
    },
    {
      "HID": "8f4f0c80-b75f-11ea-9c42-15965300ded2",
      "station": "PERTHRadio",
      "starttime": 1593143164231
    },
    {
      "HID": "b6cfcfb0-b75f-11ea-893e-33873840b421",
      "station": "PERTHRadio",
      "starttime": 1593143230506
    },
    {
      "HID": "d3c37680-b75f-11ea-9e35-8b493642ae94",
      "station": "PERTHRadio",
      "starttime": 1593143279079
    },
    {
      "HID": "e9722710-b75f-11ea-9962-75b52c48511f",
      "station": "PERTHRadio",
      "starttime": 1593143315456
    },
    {
      "HID": "1a92c070-b760-11ea-a959-a184cc7e1bde",
      "station": "PERTHRadio",
      "starttime": 1593143397878
    },
    {
      "HID": "3146d310-b760-11ea-8069-d9d0a9c80a89",
      "station": "PERTHRadio",
      "starttime": 1593143435968
    },
    {
      "HID": "569b4060-b760-11ea-8fb3-57fc4edb6600",
      "station": "PERTHRadio",
      "starttime": 1593143498598
    },
    {
      "HID": "6d8b0960-b795-11ea-9545-fb36a6e7214b",
      "station": "DRN1",
      "starttime": 1593166300405,
      "endtime": 1593166301643
    },
    {
      "HID": "1f5d3430-b839-11ea-a52e-81ad305f80cc",
      "station": "DRN1",
      "starttime": 1593236606706,
      "endtime": 1593236607927
    }
  ]
}
RussellHarrower
  • 6,470
  • 21
  • 102
  • 204

1 Answers1

1

You need to filter the array.

{
    $project:{
        "history":{
            $filter:{
                input:"$history",
                cond:
                    {$gte:["$$this.starttime",1593236606706]}               
            }
        }
    }
}

Working Mongo playground

varman
  • 8,704
  • 5
  • 19
  • 53
  • I tried your code and it gave me this error. `must be an array of documents like '[ {_id: 1}, {_id: 2} ]'` I have added our sample data, but also I am going to see if I can modify your code a little to get it to work I can see what you mean by your answer. – RussellHarrower Jul 25 '20 at 04:33
  • I gave my anser based on what you provided, and working fine. Better you modify the question to update how you structured the data – varman Jul 25 '20 at 04:34
  • I have updated the question. Sorry I thought I was being clear enough. My bad. Your code does work. – RussellHarrower Jul 25 '20 at 04:35
  • sorry my browser did not publish the update - now done – RussellHarrower Jul 25 '20 at 04:39
  • I dont get any error, the data u provided https://mongoplayground.net/p/iHM7GGJM3e4 – varman Jul 25 '20 at 04:42