Create FunctionContributor for postgres jsonb operators

Hibernate ORM
1

Postgres has special operators for working with json/jsonb data ( PostgreSQL: Documentation: 9.5: JSON Functions and Operators ). The native sql query that I want to call from jpql is checking existence in a jsonb array:

# select '["a", "b"]'::jsonb ? 'a';
 ?column? 
----------
 t
(1 row)

I read that I can do this by using a FunctionContributor. The normal path seems to be something like this:

  @Override
  public void contributeFunctions(FunctionContributions functionContributions) {
    BasicTypeRegistry typeRegistry = functionContributions.getTypeConfiguration().getBasicTypeRegistry();
    BasicType<Boolean> booleanType = typeRegistry.getRegisteredType(Boolean.class);
    functionContributions.getFunctionRegistry().registerPattern(
        "jsonb_contains",
        "?1 \\? ?2",
        booleanType);
  }

However, the pattern parsing doesn’t seem to be able to handle a question mark being part of the pattern:

Caused by: java.lang.NumberFormatException: For input string: ""
	at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:67)
	at java.base/java.lang.Integer.parseInt(Integer.java:672)
	at java.base/java.lang.Integer.parseInt(Integer.java:778)
	at org.hibernate.query.sqm.produce.function.internal.PatternRenderer.<init>(PatternRenderer.java:100)
	at org.hibernate.query.sqm.produce.function.internal.PatternRenderer.<init>(PatternRenderer.java:52)
	at org.hibernate.query.sqm.produce.function.PatternFunctionDescriptorBuilder.descriptor(PatternFunctionDescriptorBuilder.java:92)
	at org.hibernate.query.sqm.produce.function.PatternFunctionDescriptorBuilder.register(PatternFunctionDescriptorBuilder.java:88)
	at org.hibernate.query.sqm.function.SqmFunctionRegistry.registerPattern(SqmFunctionRegistry.java:115)
	at com.my.integrations.hibernate.MyFunctionContributor.contributeFunctions(MyFunctionContributor.java:15)

I then attempted to extend SqmFunction, but I couldn’t figure out how to make that work because the constructor required all these SqmFunctionDescriptor functionDescriptor, SqmExpressible type, List arguments, NodeBuilder criteriaBuilder. I’d appreciate any help getting this set up.

2

Like pointed out by Gavin on the Jira you created, you have to register a custom SqmFunctionDescriptor. Try extending AbstractSqmSelfRenderingFunctionDescriptor for that purpose.