Introduction

Spring Data JPA MongoDB Expressions is a library that allows you to query Spring Data JPA Repositories using MongoDB Query Language.
It parses (a subset of) MongoDB expressions (which itself a initiative and easy to learn Language) and convert them to Spring Data JPA Specifications to be used with Spring Data JPA Repositories.

It is specially useful for so many JavaScript clients that like to build queries using convenient MongoDB expressions and send the query to the Spring-based API to be processed by the Repository.

The idea is that you can build the MongoDB query-like JSON from the frontend app and pass it to the Spring Controller, and then optionally enrich it with additional conditions and pass it to the JPA Repository, in which the MongoDB query will be translated automatically to JPA specification and executed.

How it works

Spring Data JPA MongoDB Expressions converts the MongoDB queries into SQL queries. So the following MongoDB query:

{ "status": "A", "qty": { "$lt": 30 } }

will be translated to.[1]:

SELECT * FROM inventory WHERE status = "A" AND qty < 30
Tip
 You can create MongoDB queries using simple javascript code see How to Build the Expressions

Getting Started

It’s really easy to get started with Spring Data JPA MongoDB Expressions. This section shows you how.

Installation

Add the following dependency in your Spring/Spring Boot project, where you already have spring-data-jpa dependency added.

Maven

<dependency>
  <groupId>com.github.mhewedy</groupId>
  <artifactId>spring-data-jpa-mongodb-expressions</artifactId>
  <version>0.1.3</version>
</dependency>

Gradle

implementation 'com.github.mhewedy:spring-data-jpa-mongodb-expressions:0.1.3'
Important
 Spring Data JPA MongoDB Expressions will not bring any dependencies with it - this is by design - so to avoid version overlap in the dependencies. So make sure to setup your spring project to include the proper version of Spring Data JPA.

How to start

Three easy steps you need to do to be able to use Spring Data JPA MongoDB Expressions:

Note
 All public APIs (classes and interfaces) of Spring Data JPA MongoDB Expressions are in the package com.github.mhewedy.expressions.*

  1. You need to customize the base repository to be the ExpressionsRepositoryImpl:

    @SpringBootApplication
@EnableJpaRepositories(repositoryBaseClass = ExpressionsRepositoryImpl.class)
public class MyApplication {

}

    You can learn more about customizing the base repository in the spring data jpa documentations

  2. Change the parent repository of your JPA repositories to ExpressionsRepository

    @Repository
public interface EmployeeRepository extends ExpressionsRepository<Employee, Long> {
}

  3. Modify the search controller to accept Expressions in its parameter list:

    @PostMapping("/search")
public ResponseEntity<Page<EmployeeDto>> search(@RequestBody Expressions expressions,
                                                Pageable pageable) {
    return ok().body(
                employeeRepository.findAll(expressions, pageable).map(employeeMapper::toDto)
        );
}

And that’s it, you can now send Mongodb-like json queries to the API. (see Query Specifications )

Literal Handling Mode in Hibernate

As the time of writing, Hibernate by default using a Literal Handling mode of AUTO when using Criteria API, which means Criteria queries uses bind parameters for any literal that is not a numeric value. You can choose to override it by setting the JPA property hibernate.criteria.literal_handling_mode to bind.

application.yaml
spring:
  jpa:
    properties:
      'hibernate.criteria.literal_handling_mode': bind

Public API

Spring Data JPA MongoDB Expressions. has 3 main public APIs (one interface and two classes) that you need to be aware of .

ExpressionsRepository interface

public interface ExpressionsRepository<T, ID> extends JpaRepository<T, ID> {

    List<T> findAll(Expressions expressions);

    List<T> findAll(Expressions expressions, Sort sort);

    Page<T> findAll(Expressions expressions, Pageable pageable);

    long count(Expressions expressions);
}

com.github.mhewedy.expressions.ExpressionsRepository is the interface that your Repositories will need to extend and use its methods to pass Expressions objects received from the Rest Controller.

As seen, you can pass Pageable object for sorting and paging as well.

Expressions class

com.github.mhewedy.expressions.Expressions class is the class used in the controller method to deserialize the query JSON object into it. it will hold all the conditions passed from the frontend app.

The user can add more conditions at the backend (e.g. to enforce more restrictions). see javadoc.

Here’s the public API for the Expressions class:

public class Expressions extends HashMap<String, Object> {

    public Expressions or(Expression expression) {}

    public Expressions and(Expression expression) {}
}

As shown above, it is mainly used to add ORed or ANDed expressions for an existing instance (usually deserialized from the JSON representation sent by the frontend app).

Note
 The two public methods in Expressions object accepts objects of type Expression

Expression class

One more class is com.github.mhewedy.expressions.Expression which represents a single expression that can be added to the Expressions object.

Here’s the public API for the Expression class:

public abstract class Expression {

    public static Expression of(String field, Operator operator, String value) {}

    public static Expression of(String field, Operator operator, Number value) {}

    // .....
    // .....

    public static Expression and(Expression... expressions) {}

    public static Expression or(Expression... expressions) {}

    public Expression and(Expression expression) {}

    public Expression or(Expression expression) {}

    public Expressions build() {}
}

As shown, it is used mainly as a static factory to create Expression instance then convert it to Expressions using the build method or pass it to one of the two methods of the Expressions class that accept the Expression object.

Examples:

  • Using Expression static builder methods to create a single Expression and ANDing it to the Expressions object:

Expressions expressions =  ... // accepted as a parameter for the controller method

expressions.and(Expression.or(
        Expression.of("lastName", Operator.$eq, "ibrahim"),
        Expression.of("age", Operator.$in, 10, 30)
));

  • Using Expression static builder methods with build method to create Expressions object:

Expressions expressions = Expression.of("lastName", Operator.$eq, "ibrahim")
    .and(Expression.or(
            Expression.of("age", Operator.$in, 10, 20),
            Expression.of("birthDate", Operator.$lt, LocalDate.of(1980, 1, 1)))
    ).build();

Query Specifications

From its name, Spring Data JPA MongoDB Expressions inspired by MongoDB Query Language and uses a subset of the language and converts it to Spring Data JPA Specifications under the hood.

In this section we will see by example all the supported features started by basic querying to comparision operators to joins and other features.

Overview

Before we go into details of the query language, we will go through the steps it takes to convert JSON-encoded MongoDB-like Query to SQL query.

High Level Overview
Figure 1. High Level Operations
The first step (yellow)

The query is created at the frontend app (Angular, React, Vue, etc…​) in JSON format according to MongoDB Query language rules and as will be shown in this section.

The second step (green)

The JSON is being deserialized into com.github.mhewedy.expressions.Expressions object and thus can be passed to your Repository (the one the extends com.github.mhewedy.expressions.ExpressionsRepository).

This intermediate step allows you to add additional conditions easily on the deserialized Expressions object as it is not uncommon you need to restrict the query passed from the frontend.

Note
 You can see the Public API for details on how to use the Expressions and Expression objects.
The third step (red)

Where the Expressions object is being converted into Spring Data JPA Specification object and then Spring Data JPA along with the underlying Persistence provider will take care of the rest (including paging and sorting, etc…​).

Queries

To get an idea about the queries in this section, here’s the domain that the queries will apply on:

Sample Domain Model
Figure 2. Sample domain model
Note
 You might need to have a look on the supported operators list before proceed to the next section.

Basic Queries

  1. Basic query:

    {
  "lastName": "ibrahim",
  "birthDate": {"$lte": "1985-10-10"}
}

    Generated SQL:

    ... where last_name=? and birth_date<=?
    Default Comparison and Logical Operators

    In MongoDB, the default comparison operator if omitted is $eq (equals) and the default logical operator between two expressions is $and (AND). This is why the generated sql compares last_name using $eq operator (equals) and then use the $and (AND) operator between the two expressions.
    You can know more about MongoDB query language from this link.

  2. Basic queries with comparison operators:

    {
  "lastName": "ibrahim",
  "$and": [
    {
      "birthDate": {"$gt": "1981-01-01"}
    },
    {
      "birthDate": {"$lte": "1985-10-10"}
    }
  ]
}

    Generated SQL:

    ... where last_name=? and birth_date>? and birth_date<=?
    Tip
    		 In case of passing a string value into a field of type java.time API (e.g. LocalDate, Instant, etc…​), the method parse on the corresponding type is responsible of convert the string into an object of that type.
    Note
    		 However the default logical operator is $and, in the above example we had to add it explicitly, this is because in JSON you cannot have duplicated object keys (in this case would be birthDate).

  3. Send search query with no conditions:

    {}

    Generated SQL:

    ... where ?=1
    Note
    		 ? in the query parameter above is bound to the value true

  4. Search with null value (is null and is not null):

    {
  "firstName": null
}
    // in the controller:
expressions.or(Expression.of("lastName", Operator.$ne, (String) null));

    Generated SQL:

    ... where first_name is null or last_name is not null

Logical operators

According to MongoDB query language, the default logical operator if omitted is the $and operator. The following are examples of complex logical operators that follows operator precedence.

  1. Complex case with multiple OR and AND Expressions:

    {
  "$or": [
    {
      "lastName": "ibrahim"
    },
    {
      "$and": [
        {
          "firstName": "mostafa"
        },
        {
          "birthDate": {
            "$gt": "1990-01-01"
          }
        }
      ]
    }
  ]
}

    Generated SQL:

    ... where last_name = ? or first_name = ? and birth_date > ?

  2. Complex case with multiple OR and AND Expressions:

    {
  "$and": [
    {
      "lastName": "ibrahim"
    },
    {
      "$or": [
        {
          "firstName": "ahmed"
        },
        {
          "birthDate": {
            "$lt": "1990-01-01"
          }
        }
      ]
    }
  ]
}

    Generated SQL:

    ... where last_name = ? and (first_name = ? or birth_date < ?)

Text Operators

  1. Using Contains for search in fields:

    {
  "lastName": {
    "$contains": "rah"
  }
}

    Generated SQL:

    ... where last_name like ?

  2. Using Contains ignore case for search in fields:

    {
  "lastName": {
    "$icontains": "Rah"
  }
}

    Generated SQL:

    ... where lower(last_name) like ?

  3. Using contains for number fields:

    {
  "age": {
    "$contains": "0"
  }
}

    Generated SQL:

    ... from employee e where cast(e.age as varchar(255)) like ?
Note
 Other text operators are supported as well, such as $start, $end, $istart, $iend
Tip
 the i character at the start of the text operator donates case-insensitive nature of the operator.

Paging and sorting

Due to being built on top of Spring Data JPA, Spring Data JPA MongoDB Expressions library is fully compatible with the Paging and Sorting features of Spring Data JPA.

  1. Paging and sorting in the url:

    /api/search?page=0&size=20&sort=id,desc

  2. Paging and sorting in code:

    {
  "hireDate": {"$lt": "2021-01-01T00:00:00Z"}
}
    // in the controller:
Pageable pageable = PageRequest.of(0, 3, Sort.by("firstName").descending()); // or from the controller method parameters

Page<Employee> employeeList = employeeRepository.findAll(expressions, pageable);

    Generated SQL:

    ... where hire_date<? order by first_name desc limit ?

Joins

Many to one

  1. Many to one Join:

    {
  "lastName": "ibrahim",
  "department.name": {"$contains":  "sw"}
}

    Generated SQL:

    ... from employee e inner join department d on e.department_id=d.id where e.last_name=? and (d.name like ?)

  2. Many to one Join with IN queries:

    {
  "department.name": {"$in":  ["hr", "sw arch"]}
}

    Generated SQL:

    ... employee e inner join department d on e.department_id=d.id where d.name in (? , ?)

  3. Using Many to one Join with deep nested level:

    {
  "lastName": "ibrahim",
  "department.city.name": "cairo"
}

    Generated SQL:

    ... from employee e inner join department d on e.department_id=d.id inner join city c on d.city_id=c.id where e.last_name=? and c.name=?

  4. Many to One with multiple fields of the One association in the where condition :

    {
  "lastName": "ibrahim",
  "department.id": 10,
  "department.name": "hr"
}

    Generated SQL:

    ... from employee e inner join department d on e.department_id=d.id where e.last_name=? and d.id=? and d.name=?

  5. Many to one where association is null:

    {
  "department": null
}

    Generated SQL:

    ... from employee e where e.department is null
One to Many

  1. Using One to Many join:

    {
  "tasks.name": {"$contains":  "fix"}
}

    Generated SQL:

    ... from employee e inner join task t on e.id=t.employee_id where t.name like ?
    Note
    		 The distinct keyword is being used in the queries to eliminate duplicates in case of one-to-many and many-to-many joins.
Tip
 Sometimes the join capabilities is not enough or the not best way for the use case you trying to implement, in such case you can create a database view and map it to an JPA Entity and then build your search on it.
Tip
 The returned properties are the properties of the primary Entity, which means the projection is not supported due to limitation in spring-data-jpa addressed in this bug, until it is fixed and if you need to return properties from other entities involved in the join, you need to follow the database _view workaround mentioned in the previous tip.

Embedded

  1. Using embedded fields:

    {
  "name.ar": "ahmed ar",
  "department.name": "hr"
}

    Generated SQL:

    ... from employee e inner join department d on e.department_id=d.id where e.employee_name_ar=? and d.name=?

Enums

  1. Passing enums as Integer ordinal:

    {
  "tasks.status": {"$in":  [0, 1]}
}

    Generated SQL:

    ... from employee e inner join task t on e.id=t.employee_id where t.status in (? , ?)

  2. Passing enums as string enum names:

    {
  "tasks.status": {"$nin":  ["ACTIVE"]}
}

    Generated SQL:

    ... from employee e inner join task t on e.id=t.employee_id where t.status not in (?)

UUID

  1. querying against UUID fields:

    {
  "serial": "2dfb7bc7-38a6-4826-b6d3-297969d17244"
}

    Generated SQL:

    ... where e.serial=?

Operators

The following is the list of supported operators:

Table 1. Operators List
Operator Description

$eq

col = val (if val is null then ⇒ col is null)

$ne

col <> val (if val is null then ⇒ col is not null)

$ieq

lower(col) = lower(val)

$gt

col > val

$gte

col >= val

$lt

col < val

$lte

col ⇐ val

$start

col like 'val%'

$end

col like '%val'

$contains

col like '%val%'

$istart

lower(col) like 'lower(val)%'

$iend

lower(col) like '%lower(val)'

$icontains

lower(col) like '%lower(val)%'

$in

col in (val1, val2, …​)

$nin

col not in (val1, val2, …​)

$or

expr1 or expr2

$and

expr1 and expr2

How to Build the Expressions

Building the expression on the frontend using Javascript is easy.

Example:
const q = {};

q.status = 'A';
q.qty = { "$lt": 30 };

console.log(JSON.stringify(q, null, 4));

Output:

{
    "status": "A",
    "qty": {
        "$lt": 30
    }
}
Another example:
let q = {};

q.lastName = "ibrahim";
q.$and = [
{ birthDate: { $gt: "1981-01-03" } },
{ birthDate: { $lte: "1981-01-03" } }
];

console.log(JSON.stringify(q, null, 4));

Output:

{
    "lastName": "ibrahim",
    "$and": [
        {
            "birthDate": {
                "$gt": "1981-01-03"
            }
        },
        {
            "birthDate": {
                "$lte": "1981-01-03"
            }
        }
    ]
}
1. This is rought SQL code, as usually values are passed parameterized in the where condition.