SoftInstigate / restheart

Rapid API Development with MongoDB
https://restheart.org
GNU Affero General Public License v3.0
807 stars 171 forks source link

Problem using aggregations #363

Closed trzepak1916 closed 5 years ago

trzepak1916 commented 5 years ago

Hi, I'm using Restheart v 4.0.0 and I'm having a problem to create aggregations.

Expected Behavior

Ability to create and use $lookup aggregation using Restheart v 4.0.0.

Current Behavior

Creating the aggregation results in creation of an object in the target collection, it does not allow to get the "joined" object, as in the example.

Context

I'm trying to make a join that will allow to get order with the person e.g.:

{
    "_id": "5d89f2fc2306656ad4ce7c9d",
    "_etag": "5d89f2fc2306656ad4ce7c9c",
    "orderId": 1
    "orderNumber": 1234
    "personId": 2
    "person": [
        {
            _id: 5d89f2712306656ad4ce7c9a
            _etag: 5d89f2712306656ad4ce7c98
            firstName: "John",
            lastName: "Smith",
            personId: 2
        }
    ]
}

I can accomplish the above result, setting the following Aggregation using MongoDB Compass (in the Aggregations tab on the orders collection):

{
  from: 'persons',
  localField: 'personId',
  foreignField: 'personId',
  as: 'person'
}

Environment

RestHeart 4.0.0 deployed in Docker Container with MongoDB 4.0.12 Community.

Steps to Reproduce

(NOTE: all posts created using httpie)

  1. I have created two collections: persons and orders in MongoDB. http -a a:a PUT 127.0.0.1:8080/persons http -a a:a PUT 127.0.0.1:8080/orders
  2. I have populated persons with following two objects:
    {
    "personId": 1,
    "lastName": "Kowalski",
    "firstName": "Jan"
    }
    {
    "personId": 2,
    "lastName": "Smith",
    "firstName": "John"
    }
  3. I have populated orders with following three objects:
    {
    "orderId": 1,
    "orderNumber": 1234,
    "personId": 2
    }, {
    "orderId": 2,
    "orderNumber": 9876,
    "personId": 1
    }, {
    "orderId": 3,
    "orderNumber": 3455,
    "personId": 1
    }
  4. I have created an aggregation on orders collection to "join" it with persons table invoking: http -a a:a PUT 127.0.0.1:8080/orders/join aggrs:='[{\"stages\":[{\"_$lookup\": {\"from\": \"persons\",\"localField\": \"personId\",\"foreignField\": \"personId\",\"as\": \"person\"}}],\"type\": \"pipeline\",\"uri\": \"ordersWithPersons\"}]' Response: HTTP 201 Created
  5. I have checked created aggregation invoking http -a a:a 127.0.0.1:8080/orders/join Response: HTTP 200 with the following body
    {
    "_etag": {
        "$oid": "5d89f4c02306656ad4ce7ca2"
    },
    "_id": "join",
    "aggrs": [
        {
            "stages": [
                {
                    "_$lookup": {
                        "as": "person",
                        "foreignField": "personId",
                        "from": "persons",
                        "localField": "personId"
                    }
                }
            ],
            "type": "pipeline",
            "uri": "ordersWithPersons"
        }
    ]
    }
  6. I have tried to use created aggregation by invoking: http -a a:a 127.0.0.1:8080/orders/join/_aggrs/ordersWithPersons but it returned the same response as in 5.
    {
    "_etag": {
        "$oid": "5d89f4c02306656ad4ce7ca2"
    },
    "_id": "join",
    "aggrs": [
        {
            "stages": [
                {
                    "_$lookup": {
                        "as": "person",
                        "foreignField": "personId",
                        "from": "persons",
                        "localField": "personId"
                    }
                }
            ],
            "type": "pipeline",
            "uri": "ordersWithPersons"
        }
    ]
    }
  7. I have noticed that the object returned in 5. and 6. is stored in the "orders" collection as other orders created in 2.

Remark

I have read #280, Aggregations section of the documentation and thread on stackoverflow and I'm still not able to solve my problem.

Question

What am I doing wrong? \ Are the aggregations still supported the way they are described in the documentation? \ What is the expected behavior when creating an aggregation using Restheart - where should it be stored? Should it be the same collection as the collection which the aggregation is created for?

ujibang commented 5 years ago

the aggrs property must be a collection metadata, i.e. you need to PATCH the collection (not create a document in the collection):

from https://restheart.org/docs/aggregations/: In RESTHeart, not only documents but also dbs and collections have properties. Some properties are metadata, i.e. they have a special meaning for RESTheart that influences its behavior.

The collection metadata property aggrs allows to declare aggregation operations and bind them to given URI.

$ http -a a:a PATCH 127.0.0.1:8080/persons aggrs:='[{"stages":[{"_$lookup": {"from": "persons","localField": "personId","foreignField": "personId","as": "person"}}],"type": "pipeline","uri": "ordersWithPersons"}]'

note that the URI is /persons. From you $lookup aggregation, the local collection is persons, not orders

you can see the collection metadata as follows:

$ http -a a:a GET :8080/persons/_meta

{
    "_etag": {
        "$oid": "5d8b1dac71fe752a9bfbc0ea"
    },
    "_id": "_meta",
    "aggrs": [
        {
            "stages": [
                {
                    "_$lookup": {
                        "as": "person",
                        "foreignField": "personId",
                        "from": "persons",
                        "localField": "personId"
                    }
                }
            ],
            "type": "pipeline",
            "uri": "ordersWithPersons"
        }
    ]
}

You can now execute the aggregation with:

$ http -a a:a GET :8080/persons/_aggrs/ordersWithPersons

[
    {
        "_etag": {
            "$oid": "5d8b1c3171fe752a9bfbc0e6"
        },
        "_id": {
            "$oid": "5d8b1c3171fe752a9bfbc0e7"
        },
        "firstName": "Jan",
        "lastName": "Kowalski",
        "person": [
            {
                "_etag": {
                    "$oid": "5d8b1c3171fe752a9bfbc0e6"
                },
                "_id": {
                    "$oid": "5d8b1c3171fe752a9bfbc0e7"
                },
                "firstName": "Jan",
                "lastName": "Kowalski",
                "personId": 1
            }
        ],
        "personId": 1
    },
    {
        "_etag": {
            "$oid": "5d8b1c3171fe752a9bfbc0e6"
        },
        "_id": {
            "$oid": "5d8b1c3171fe752a9bfbc0e8"
        },
        "firstName": "John",
        "lastName": "Smith",
        "person": [
            {
                "_etag": {
                    "$oid": "5d8b1c3171fe752a9bfbc0e6"
                },
                "_id": {
                    "$oid": "5d8b1c3171fe752a9bfbc0e8"
                },
                "firstName": "John",
                "lastName": "Smith",
                "personId": 2
            }
        ],
        "personId": 2
    }
]

the aggregation URI is /persons/_aggrs/ordersWithPersons as specified by the uri property in the aggregation definition object

trzepak1916 commented 5 years ago

Thank you for your help, now I'm able to achieve what I want.

Anyway I think that there was a small misunderstanding, because I wanted to join orders with persons (instead of joining persons with persons, as it is in your example).

After sending the following requests based on your example, I have achieved what I wanted:

$ http -a a:a PATCH 127.0.0.1:8080/orders aggrs:='[{"stages":[{"_$lookup": {"from": "persons","localField": "personId","foreignField": "personId","as": "person"}}],"type": "pipeline","uri": "ordersWithPersons"}]' 

above request has created a following collection metadata entry:

$ http -a a:a GET :8080/orders/_meta

{
    "_id": "_meta",
    "_etag": {
        "$oid": "5d8c5d980d93d95c275c1605"
    },
    "aggrs": [
        {
            "stages": [
                {
                    "_$lookup": {
                        "from": "persons",
                        "localField": "personId",
                        "foreignField": "personId",
                        "as": "person"
                    }
                }
            ],
            "type": "pipeline",
            "uri": "ordersWithPersons"
        }
    ]
}

and I have executed the aggregation the following way:

$ http -a a:a GET :8080/orders/_aggrs/ordersWithPersons

[
    {
        "_id": {
            "$oid": "5d8c5d0e0d93d95c275c1602"
        },
        "_etag": {
            "$oid": "5d8c5d0e0d93d95c275c1601"
        },
        "orderId": 1,
        "orderNumber": 1234,
        "personId": 2,
        "person": [
            {
                "_id": {
                    "$oid": "5d8c5cf80d93d95c275c15ff"
                },
                "_etag": {
                    "$oid": "5d8c5cf80d93d95c275c15fd"
                },
                "firstName": "John",
                "lastName": "Smith",
                "personId": 2
            }
        ]
    },
    {
        "_id": {
            "$oid": "5d8c5d0e0d93d95c275c1603"
        },
        "_etag": {
            "$oid": "5d8c5d0e0d93d95c275c1601"
        },
        "orderId": 2,
        "orderNumber": 9876,
        "personId": 1,
        "person": [
            {
                "_id": {
                    "$oid": "5d8c5cf80d93d95c275c15fe"
                },
                "_etag": {
                    "$oid": "5d8c5cf80d93d95c275c15fd"
                },
                "firstName": "Jan",
                "lastName": "Kowalski",
                "personId": 1
            }
        ]
    },
    {
        "_id": {
            "$oid": "5d8c5d0e0d93d95c275c1604"
        },
        "_etag": {
            "$oid": "5d8c5d0e0d93d95c275c1601"
        },
        "orderId": 3,
        "orderNumber": 3455,
        "personId": 1,
        "person": [
            {
                "_id": {
                    "$oid": "5d8c5cf80d93d95c275c15fe"
                },
                "_etag": {
                    "$oid": "5d8c5cf80d93d95c275c15fd"
                },
                "firstName": "Jan",
                "lastName": "Kowalski",
                "personId": 1
            }
        ]
    }
]

and I have achieved what I wanted - orders filled with the full information about person (orders joined by personId field with persons).

Thank you for your help