pymongo use sort with $elemMatch doesn't work

Question

I had some data sample like these:

[
{
    "_id": 1,
    "host": "host1",
    "type": "type1",
    "data": [
        {
            "t": 10000,
            "v": 90
        },
        {
            "t": 10001,
            "v": 94
        },
    ]
},
{
    "_id": 2,
    "host": "host1",
    "type": "type1",
    "data": [
        {
            "t": 10000,
            "v": 99
        },
        {
            "t": 10001,
            "v": 93
        },
    ]
},
{
    "_id": 3,
    "host": "host1",
    "type": "type1",
    "data": [
        {
            "t": 10000,
            "v": 94
        },
        {
            "t": 10001,
            "v": 100
        },
    ]
}]

my query is:

my_filter = {'host': 'host1', 'type': 'type1', 'data': {'$elemMatch': {'t': 10000}}}
projection = {'host': 1, 'type': 1, 'data': {'$elemMatch': {'t': 10000}}}
sort_key = 'data.0.v'

rs = db.find(my_filter, projection).sort(sort_key, 1)

rs = list(rs)
for v in rs:
    print(v["data"][0]['v'])

but output like that sort doest work:

Notice:

now use: Python==3.6.9, pymongo==3.10.1, MongoDB==4.2.6
the length of documents is 10000, the length of nested array is 1440

I only need data that meet the conditions in Nested Array, not all, because it might be a large array
I need sort data, but I can't change the write order
I also used $aggregate, but when data is large, it performance is bad, so I hope do some operation with find()

$aggregate like these:

    rs = db.aggregate([
    {"$match": {'host': 'host1', 'type': 'type1', 'data': {'$elemMatch': {'t': 10000}}}},
    {"$project": {'host': 1, 'type': 1,
                  'data': {"$filter": {
                      "input": "$data",
                      "as": "data",
                      "cond": {"$eq": ["$$data.t", 10000]}}
                  }
                  }},
    {"$sort": {'data.0.v': 1}}])

sorry for my poor English, but is here a good solution?

What versions of Mongodb, python and pymongo are you running? — Belly Buster
– Belly Buster, Commented Dec 22, 2020 at 9:37
Because there are too many keys in the embedded array, i think it is not easy to create index for each requirement，so i try to find another way — Wynter
– Wynter, Commented Dec 22, 2020 at 10:58

Belly Buster · Accepted Answer · 2020-12-22 12:29:18Z

I couldn't reproduce your issue using the software versions you mention. If you have bash and docker you could see if your results are different:

PROJECT_NAME=sort_with_elemmatch

MONGODB_VERSION=4.2.6
PYTHON_VERSION=3.6.9
PYMONGO_VERSION=3.10.1

docker network create local_temp 2> /dev/null
docker run --rm --network local_temp -d --name mongodb_temp mongo:${MONGODB_VERSION}

cd "$(mktemp -d)" || exit

cat << EOF > requirements.txt
pymongo==${PYMONGO_VERSION}
EOF

cat << 'EOF' > ${PROJECT_NAME}.py
from pymongo import MongoClient
from random import randint

db = MongoClient('mongodb://mongodb_temp')['mydatabase'].mycollection

for i in range(20):
    db.insert_one(
    {
        "host": "host1",
        "type": "type1",
        "data": [
            {
                "t": 10000,
                "v": randint(0, 100)
            },
            {
                "t": 10001,
                "v": randint(0, 100)
            },
        ]
    })

my_filter = {'host': 'host1', 'type': 'type1', 'data': {'$elemMatch': {'t': 10000}}}
projection = {'host': 1, 'type': 1, 'data': {'$elemMatch': {'t': 10000}}}
sort_key = 'data.0.v'

rs = db.find(my_filter, projection).sort(sort_key, 1)

rs = list(rs)
for v in rs:
    print(v["data"][0]['v'])
EOF

cat << EOF > Dockerfile
FROM python:${PYTHON_VERSION}
COPY ./* /
RUN pip install -r /requirements.txt
CMD ["python", "${PROJECT_NAME}.py"]
EOF

docker build --tag ${PROJECT_NAME}:latest .
docker run --rm --network local_temp --name ${PROJECT_NAME} ${PROJECT_NAME}:latest
docker stop "$(docker ps -a -q --filter name=mongodb_temp)" > /dev/null
docker image rm ${PROJECT_NAME}:latest > /dev/null
docker network rm local_temp > /dev/null

prints:

thank you for trying，i found the question, please look at answer below

Wynter · Accepted Answer · 2020-12-23 07:57:29Z

0

I found the problem, This seems to be related to the order of keys in the embedded array:

    for i in range(20):
        data_0, data_1 = {"t": 10000, "v": random.randint(0, 100)}, {"t": 10001, "v": random.randint(0, 100)}
        insert_d = {
            "host": "host1",
            "type": "type1",
            "data": [data_0, data_1] if i != 10 else [data_1, data_0]
        }
        db.insert_one(insert_d)

    my_filter = {'host': 'host1', 'type': 'type1', 'data': {'$elemMatch': {'t': 10000}}}
    projection = {'host': 1, 'type': 1, 'data': {'$elemMatch': {'t': 10000}}}
    sort_key = 'data.0.v'

    rs = db.find(my_filter, projection).sort(sort_key, 1)

    rs = list(rs)
    for v in rs:
        print(v["data"][0]['v'])

if you try this, you will found sort doesnt work as expected, Most of the values are ordered, but one is unordered, so I want to know how sort works

edited Dec 23, 2020 at 7:57

answered Dec 23, 2020 at 7:46

Wynter

1031 silver badge4 bronze badges

1 Comment

Belly Buster Over a year ago

I hunted around and maybe this might help ... stackoverflow.com/questions/28889240/…

Collectives™ on Stack Overflow

pymongo use sort with $elemMatch doesn't work

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related