jpa-type-flattenedjson

Simulate a new datatype FlattenedJson in database based on the AttributeConverter feature of JPA 2.1 with Hibernate, QueryDsl, Jackson JSON and JsonFlattener.

Goal

• Make all kinds of relational databases to support JSON data format with as little effort as possible.
  
• Allow user to search arbitrary JSON data through QueryDsl JPA without using database specified functions(Ex: JSON_CONTAINS).

Maven Repository

<dependency>
	<groupId>com.github.wnameless</groupId>
	<artifactId>jpa-type-flattenedjson</artifactId>
	<version>0.2.2</version>
</dependency>

Concept Brief

Normally JSON format can not be queried directly with SQL, it's required the database to provide special functions to search JSON data. For example, the JSON_CONTAINS function in MySQL database.

However, all those special functions are not well supported by all RDBMS and it tends to break the SQL convention somehow.

AttributeConverter was introduced in JPA 2.1. It allows any field of an entity class to be converted to JSON string which can also be stored as Varchar in all databases.

Applying JsonFlattener on stored JSON strings makes us possible to search a flattened JSON data by regular SQL LIKE or REGEXP related function without losing performance.

Usually to search a JSON data with regexp is a bad idea because JSON is not regular. Since it allows arbitrary embedding of nested data, it is almost near context-free. But a flattened JSON is much more regular, so using regexp on FlattenedJson is way more efficient and easier.

Howto

Turn arbitrary objects into flattened JSON strings and store them into database as Character datatype.

@Entity
public class TestModel {

  @GeneratedValue
  @Id
  Long id;

  @Column(length = 4000)
  @Convert(converter = JsonNodeConverter.class)
  JsonNode props; // JsonNode is from jackson-databind library

  @Column(length = 4000)
  // Implemented by extending the abstract ToFlattenedJsonConverter class
  @Convert(converter = TestModelAttrConverter.class)
  TestModelAttr testAttr;

}

public class TestModelAttr {

  private List<Integer> numbers = new ArrayList<>();

  private List<Map<String, String>> words = new ArrayList<>();

  // Getters and Setters...
}

@Autowired
TestModelRepository testModelRepo; // Spring Data

TestModel testModel = new TestModel();
testModel.setProps(FlattenedJsonTypeConfigurer.INSTANCE.getObjectMapperFactory()
  .get().readTree("{ \"abc\": { \"CBA\": 123 } }"));

TestModelAttr tma = new TestModelAttr();

tma.getNumbers().add(3);
tma.getNumbers().add(2);
tma.getNumbers().add(1);

tma.getWords().add(new HashMap() {{ put("abc", "XYZ"); }});
tma.getWords().add(new HashMap() {{ put("DEF", "uvw"); }});
model.setTestAttr(tma);

testModelRepo.save(model);

// The actual data stored in database:
// | id | props           | test_attr                                                                                |
// |----|-----------------|------------------------------------------------------------------------------------------|
// | 1  | {"abc.CBA":123} | {"numbers[0]":3,"numbers[1]":2,"numbers[2]":1,"words[0].abc":"XYZ","words[1].DEF":"uvw"} |

Query the stored data by QueryDsl with SQL LIKE and REGEXP_LIKE functions supported.
QTestModel can be generated by QueryDsl APT.

JPAQuery<TestModel> query = new JPAQuery<TestModel>(entityManager);
QTestModel qTestModel = QTestModel.testModel;

long count = query
  .from(qTestModel)
  .where(QueryDslHelper.flattenedJsonLike(qTestModel.testAttr, "numbers[0]", "3"))
  .fetchCount();
  
count = query
  .from(qTestModel)
  .where(QueryDslHelper.flattenedJsonRegexpLike(qTestModel.testAttr, "numbers[0]", "\\d+"))
  .fetchCount();

Quick Start

Annotate any field in JPA Entity class with @Convert and a converter class which extends ToFlattenedJsonConverter abstract class.

@Convert(converter = TestModelAttrConverter.class)

public class TestModelAttrConverter
    extends ToFlattenedJsonConverter<TestModelAttr> {

  @Override
  protected TypeReference<TestModelAttr> getAttributeTypeReference() {
    return new TypeReference<TestModelAttr>() {};
  }

}

Features

Because REGEXP of databases is supported in different ways, it is required a little configuration to enable this feature.
So far, Hibernate is the only ORM supported.

The following table shows all tested databases:

Database	REGEXP_LIKE	REGEXP_MATCHES	SUBSTRING
H2	☑	☐	☐
HSQLDB	☐	☑	☐
MySQL	☑	☐	☐
PostgreSQL	☐	☐	☑

Configuration (Since v0.2.0, REGEXP_MATCHES and SUBSTRING are also supported.)

Pick either of configurations listed below which fits your database:

Spring application.properties

// Add REGEX_LIKE function support to Hibernate
hibernate.metadata_builder_contributor=com.github.wnameless.jpa.type.flattenedjson.hibernate.RegexpLikeSqlFunctionContributor

// Add REGEX_MATCHES function support to Hibernate
hibernate.metadata_builder_contributor=com.github.wnameless.jpa.type.flattenedjson.hibernate.RegexpMatchesSqlFunctionContributor

// Add SUBSTRING function support to Hibernate
hibernate.metadata_builder_contributor=com.github.wnameless.jpa.type.flattenedjson.hibernate.SubstringSqlFunctionContributor

Java persistence.xml

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.github.wnameless.jpa.type.flattenedjson.hibernate.RegexpLikeSqlFunctionContributor"
</property>

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.github.wnameless.jpa.type.flattenedjson.hibernate.RegexpMatchesSqlFunctionContributor"
</property>

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.github.wnameless.jpa.type.flattenedjson.hibernate.SubstringSqlFunctionContributor"
</property>

QueryDslHelper

LIKE

This query pattern need to be provide completely.

@Autowired
TestModelRepository testModelRepo; // Spring Data
QTestModel qTestModel = QTestModel.testModel;

BooleanExpression exp = QueryDslHelper.like(qTestModel.testAttr, "'%\"numbers[0]\":3,%'");
testModelRepo.count(exp); 

Ignore case

QueryDslHelper.like(qTestModel.testAttr, "'%\"NUMBERS[0]\":3,%'", true);

FlattenedJson LIKE

Just simply provide the JSON key and value, then the LIKE query pattern is created automatically.

@Autowired
TestModelRepository testModelRepo; // Spring Data
QTestModel qTestModel = QTestModel.testModel;

BooleanExpression exp = QueryDslHelper.flattenedJsonlike(qTestModel.testAttr, "numbers[0]", "3");
testModelRepo.count(exp); 

Ignore case

QueryDslHelper.flattenedJsonlike(qTestModel.testAttr, "NUMBERS[0]", "3", true);

REGEXP_LIKE

This query pattern need to be provide completely.

JPAQuery<TestModel> query = new JPAQuery<TestModel>(entityManager);
QTestModel qTestModel = QTestModel.testModel;

BooleanExpression exp = QueryDslHelper.regexpLike(qTestModel.testAttr,
    QueryDslHelper.REGEXP_PAIR_PREFIX    // "[{,]" + "\""
    + QueryDslHelper.quoteRegExSpecialChars("numbers[0]")
    + QueryDslHelper.REGEXP_PAIR_INFIX   // "\":"
    + "\\d+"
    + QueryDslHelper.REGEXP_PAIR_SUFFIX); // "[,}]"

query.from(qTestModel).where(exp).fetchCount();

FlattenedJson REGEXP_LIKE

Just simply provide the JSON key and REGEXP of value, then the query pattern is created automatically.

JPAQuery<TestModel> query = new JPAQuery<TestModel>(entityManager);
QTestModel qTestModel = QTestModel.testModel;

BooleanExpression exp = QueryDslHelper.flattenedJsonRegexpLike(qTestModel.testAttr, "numbers[0]", "\\d+");
query.from(qTestModel).where(exp).fetchCount();

By default, the key is quoted. This can be disable by doing this:

QueryDslHelper.flattenedJsonRegexpLike(qTestModel.testAttr, "numbers\\[0\\]", "\\d+", false);

REGEXP_MATCHES

This query pattern need to be provide completely.

JPAQuery<TestModel> query = new JPAQuery<TestModel>(entityManager);
QTestModel qTestModel = QTestModel.testModel;

BooleanExpression exp = QueryDslHelper.regexpMatches(qTestModel.testAttr,
    QueryDslHelper.REGEXP_PAIR_PREFIX    // "[{,]" + "\""
    + QueryDslHelper.quoteRegExSpecialChars("numbers[0]")
    + QueryDslHelper.REGEXP_PAIR_INFIX   // "\":"
    + "\\d+"
    + QueryDslHelper.REGEXP_PAIR_SUFFIX); // "[,}]"

query.from(qTestModel).where(exp).fetchCount();

FlattenedJson REGEXP_MATCHES

Just simply provide the JSON key and REGEXP of value, then the query pattern is created automatically.

JPAQuery<TestModel> query = new JPAQuery<TestModel>(entityManager);
QTestModel qTestModel = QTestModel.testModel;

BooleanExpression exp = QueryDslHelper.flattenedJsonRegexpMatches(qTestModel.testAttr, "numbers[0]", "\\d+");
query.from(qTestModel).where(exp).fetchCount();

By default, the key is quoted. This can be disable by doing this:

QueryDslHelper.flattenedJsonRegexpMatches(qTestModel.testAttr, "numbers\\[0\\]", "\\d+", false);

SUBSTRING_MATCHES

Because we only care if the SUBSTRING MATCHES regexp pattern, not actually want to aquire the substring itself. The function is named as #substringMatches intead of #substring to avoid misunderstanding.

This query pattern need to be provide completely.

JPAQuery<TestModel> query = new JPAQuery<TestModel>(entityManager);
QTestModel qTestModel = QTestModel.testModel;

BooleanExpression exp = QueryDslHelper.substringMatches(qTestModel.testAttr,
    QueryDslHelper.REGEXP_PAIR_PREFIX    // "[{,]" + "\""
    + QueryDslHelper.quoteRegExSpecialChars("numbers[0]")
    + QueryDslHelper.REGEXP_PAIR_INFIX   // "\":"
    + "\\d+"
    + QueryDslHelper.REGEXP_PAIR_SUFFIX); // "[,}]"

query.from(qTestModel).where(exp).fetchCount();

FlattenedJson SUBSTRING_MATCHES

Just simply provide the JSON key and REGEXP of value, then the query pattern is created automatically.

JPAQuery<TestModel> query = new JPAQuery<TestModel>(entityManager);
QTestModel qTestModel = QTestModel.testModel;

BooleanExpression exp = QueryDslHelper.flattenedJsonSubstringMatches(qTestModel.testAttr, "numbers[0]", "\\d+");
query.from(qTestModel).where(exp).fetchCount();

By default, the key is quoted. This can be disable by doing this:

QueryDslHelper.flattenedJsonSubstringMatches(qTestModel.testAttr, "numbers\\[0\\]", "\\d+", false);

ToFlattenedJsonConverter

A base class to create a new JPA Converter of arbitrary type for FlattenedJson.

@Converter
public class AnyTypeConverter extends ToFlattenedJsonConverter<AnyType> {

  @Override
  protected TypeReference<AnyType> getAttributeTypeReference() {
    return new TypeReference<AnyType>() {};
  }

}

JsonNodeConverter is already provided in library.

FlattenedJsonTypeConfigurer

FlattenedJsonTypeConfigurer is an enum with a single vlaue INSTANCE which also implies it's a singleton.

FlattenedJsonTypeConfigurer.INSTANCE

FlattenedJsonType is powered by JsonFlattener.

FlattenedJsonTypeConfigurer.INSTANCE.getJsonFlattenerCustomizer();
FlattenedJsonTypeConfigurer.INSTANCE.setJsonFlattenerCustomizer(Function<JsonFlattener, JsonFlattener> jsonFlattenerCustomizer);
FlattenedJsonTypeConfigurer.INSTANCE.getJsonUnflattenerCustomizer();
FlattenedJsonTypeConfigurer.INSTANCE.setJsonUnflattenerCustomizer(Function<JsonUnflattener, JsonUnflattener> jsonUnflattenerCustomizer);

FlattenedJsonType is powered by jackson-databind as well.

FlattenedJsonTypeConfigurer.INSTANCE.getObjectMapperFactory();
FlattenedJsonTypeConfigurer.INSTANCE.setObjectMapperFactory(Supplier<ObjectMapper> objectMapperFactory);

Any modification in FlattenedJsonTypeConfigurer will take effects on the entire library.