Sorting on keys in external referenced docs

MongoDB Atlas Atlas Search
,
1

I have the below Offering class in a Java/Spring Boot app. IT has a @DBRef to Security (another document)

@Document(collection = "market")
@Getter
@Setter
@EqualsAndHashCode
@AllArgsConstructor
@NoArgsConstructor
public class Offering
{
    @Id
    String id;

    @CreatedDate
    Date createdDate;

    @LastModifiedDate
    Date modifiedDate;

    @Field(name = "askQty")
    Integer askQty;

    @Field(name = "askPrice")
    Double askPrice;

    @Field(name = "askYTW")
    Double askYTW;

    @Field(name = "type")
    String type;

    @DBRef
    Security security;
}

The Security class is:

@Document(collection = "secmaster")
@Getter
@Setter
@EqualsAndHashCode
@AllArgsConstructor
@NoArgsConstructor
public class Security
{
    @Id
    @Field(name = "_id")
    private String cusip;

    private String state;

    private String desc;

    private Double coupon;

    ...
}

Everything works as expected using OfferingRepository (which extends MongoRepository) , EXCEPT for sorting. Specifically, passing a property of the referenced class, Security, into sort params throws exceptions. Example, I can sort on ‘askQty’ (property of Offering) but not on ‘security.desc’ (property of Security).

Is there a recommended way to design such one-to-many associations and still be able to sort on properties of referenced ‘child’ entities? Seems like a very common design issue.

3

Hi @Jawaid_Hakim and welcome to the community forum.

Based on the above two classes mentioned, I tried to create some sample data around it which looks like:

Atlas atlas-xp4gev-shard-0 [primary] test> db.market.find()
[
  {
    _id: ObjectId('65c350778fe0a91980da2997'),
    DateCreated: ISODate('2000-11-01T13:45:00.000Z'),
    DateModified: ISODate('2000-11-01T13:45:00.000Z'),
    askQty: 10,
    askPrice: 500,
    askType: 'XYZ',
    security: DBRef('secmaster', ObjectId('603453d07762a41a54b0aaf7'))
  },
  {
    _id: ObjectId('65c350778fe0a91980da2998'),
    DateCreated: ISODate('2000-11-02T14:15:00.000Z'),
    DateModified: ISODate('2000-11-02T14:15:00.000Z'),
    askQty: 5,
    askPrice: 420,
    askType: 'DEF',
    security: DBRef('secmaster', ObjectId('603453d07762a41a54b0aaf8'))
  }
]
and 

Atlas atlas-xp4gev-shard-0 [primary] test> db.security.find()
[
  {
    _id: ObjectId('603453d07762a41a54b0aaf7'),
    cusip: 'Canada',
    state: 'Canada',
    desc: 'Test Security 1',
    coupon: 5.5
  },
  {
    _id: ObjectId('603453d07762a41a54b0aaf8'),
    cusip: 'Mexico',
    state: 'Mexico',
    desc: 'Test Security 2',
    coupon: 4.2
  }
]

Now, if I understand your query correctly, you are looking for a way to sort on the external collection. Have you considered using aggregation to do so ?
The documentation in spring.io that talks about using aggregations with Spring boot might help you solve the issue.

The code below:

Aggregation aggregation = Aggregation.newAggregation(
    // Perform a $lookup operation
    lookup("security", "security.$id", "_id", "result"),

    // Unwind the "result" array
    unwind("result"),

    // Sort by "desc" field in ascending order
    sort(Sort.Direction.ASC, "result.desc")
);

would help you sort on the external reference docs.

The other way would be to use custom query using the @Query operator which would help you resolve.

Please reach out in case you have further questions.

Best Regards
Aasawari