Is it possible to filter by json fields?

[lewimuchiri]

Is it possible to achieve something like this: select * from book where author->>'name' = 'John Doe';

author is a Postgres column of json type and has a field called 'name'. In JPA, this is represented as follows:

@Entity
@Table(name = "book")
@Include
public class Book {
    @Type(JsonType.class)
    @Column(columnDefinition = "JSON")
    private Author author;
    ...
}

public class Author {
    private String name;
    private String phoneNumber;
    ...
}

The goal is to filter by the fields that are present in the json column. Is this possible? Otherwise then the question would be whether it's possible to create a custom operator so that I can create ->> for querying the json fields

[aklish]

You can override the JPQL generated for a given operator or for an operator on a given model and field. There is some documentation here:

https://elide.io/pages/guide/v7/16-performance.html#jpql-fragment-override

[lewimuchiri]

The documentation mentions defining a JPQL Predicate Generator like this:

@FunctionalInterface
public interface JPQLPredicateGenerator {
    /**
     * Generate a JPQL fragment for a particular filter operator.
     * @param predicate The filter predicate
     * @param aliasGenerator Given a path, returns a JPQL alias for that path.
     * @return A JPQL fragment.
     */
    String generate(FilterPredicate predicate, Function<Path, String> aliasGenerator);
}

And then registering it with Elide like this:

FilterTranslator.registerJPQLGenerator(Operator.NOTNULL, Book.class, "title",
    (predicate, aliasGenerator) -> {
            return String.format("%s IS NOT NULL", aliasGenerator.apply(predicate.getPath()));
    }
);

But where should the above code be put? In a Configuration class or? Please give an example of how to use this code. (Forgive my newbie question)

[lewimuchiri]

I've just seen that HQL does not support querying JSON fields, and that you have to use native SQL in order to query json fields using the ->> operator (in Postgres). Since Elide does not allow registering a native query generator, does this mean that it's currently not possible to query JSON fields in Elide?

[lewimuchiri]

So I finally managed to achieve what I wanted by doing the following:

1. Create a JPQL generator for the JSON fields:

public class JsonFieldJPQLGenerator implements JPQLPredicateGenerator {
    private static final String COMMA = ", ";
    private static final String FILTER_PATH_MUST_BE_2_LEVELS =
            "Filtering field path must have at most one hierarchy level.";
    private static final String FILTER_PATH_NOT_NULL = "Filtering field path cannot be empty.";
    private static final String FILTER_ALIAS_NOT_NULL = "Filtering alias cannot be empty.";

    private final CaseAwareJPQLGenerator.Case upperOrLower;
    private final String jpqlTemplate;
    private final CaseAwareJPQLGenerator.ArgumentCount argumentCount;

    /**
     * Constructor.
     * @param jpqlTemplate A JPQL Query Fragment Template
     * @param upperOrLower UPPER, LOWER, or NONE
     * @param argumentCount ZERO, ONE, or MANY
     */
    public JsonFieldJPQLGenerator(String jpqlTemplate, CaseAwareJPQLGenerator.Case upperOrLower,
                                  CaseAwareJPQLGenerator.ArgumentCount argumentCount) {
        this.upperOrLower = upperOrLower;
        this.jpqlTemplate = jpqlTemplate;
        this.argumentCount = argumentCount;
    }

    @Override
    public String generate(FilterPredicate predicate, Function<Path, String> aliasGenerator) {
        // predicate.getPath().getPathElements() returns an immutable list,
        // so we create a new mutable list using new ArrayList<>()
        List<Path.PathElement> pathElements = new ArrayList<>(predicate.getPath().getPathElements());

        if (pathElements.size() != 2) {
            // we're only supporting one level down (i.e. parent & child)
            throw new InvalidValueException(FILTER_PATH_MUST_BE_2_LEVELS);
        }

        // Remove the last field from the Path 
        // (since we want to go up to the 2nd-last field [which will be the parent object]).
        // json_extract_path_text(...) has the path elements as separate variadic arguments.
        // For now we're only dealing with json objects with one level down only (i.e. parent and child).
        // When we get a use case for json objects with multiple levels down (e.g. parent.child.grandChild),
        // then we shall modify this algorithm so that it can smartly generate the correct HQL
        // using the json_extract_path_text(...) sql method
        pathElements.remove(pathElements.size() - 1);

        String columnAlias = aliasGenerator.apply(new Path(pathElements));
        List<FilterPredicate.FilterParameter> parameters = predicate.getParameters();

        if (StringUtils.isEmpty(columnAlias)) {
            throw new InvalidValueException(FILTER_PATH_NOT_NULL);
        }

        if (argumentCount == CaseAwareJPQLGenerator.ArgumentCount.MANY) {
            Preconditions.checkState(!parameters.isEmpty());
        } else if (argumentCount == CaseAwareJPQLGenerator.ArgumentCount.ONE) {
            Preconditions.checkArgument(parameters.size() == 1);

            if (StringUtils.isEmpty(parameters.get(0).getPlaceholder())) {
                throw new IllegalStateException(FILTER_ALIAS_NOT_NULL);
            }
        }

        return String.format(jpqlTemplate, upperOrLower.wrap(columnAlias), parameters.stream()
                .map(upperOrLower::wrap)
                .collect(Collectors.joining(COMMA)));
    }
}

2. Create a class for registering the JPQL generators

public final class JPQLGeneratorRegister {
    private JPQLGeneratorRegister() {
        // Not to be instantiated
    }

    public static void registerCustomJPQLGenerators() {
        registerJsonJPQLGenerator(StockTakeUser.class, "login");
        registerJsonJPQLGenerator(InventoryGroupingItem.class, "itemCode");
    }

    private static <T> void registerJsonJPQLGenerator(Class<T> clazz, String fieldName) {
        JPQLPredicateGenerator generator = new JsonFieldJPQLGenerator(
                "json_extract_path_text(%s, '" + fieldName + "') = (%s)",
                CaseAwareJPQLGenerator.Case.NONE, CaseAwareJPQLGenerator.ArgumentCount.MANY);

        FilterTranslator.registerJPQLGenerator(Operator.IN, ClassType.of(clazz), fieldName, generator);
    }
}

3. Call JPQLGeneratorRegister. registerCustomJPQLGenerators() from any method that you're sure will be called only once, for example in the @Configuration class where you define the Elide bean:

    @Bean
    public Elide initializeElide(EntityDictionary dictionary, DataStore dataStore, ElideConfigProperties settings) {

        ElideSettingsBuilder builder = new ElideSettingsBuilder(dataStore)
                .withEntityDictionary(dictionary)
                .withVerboseErrors()
                .withDefaultMaxPageSize(settings.getMaxPageSize())
                .withDefaultPageSize(settings.getPageSize())
                .withISO8601Dates("yyyy-MM-dd'T'HH:mm'Z'", TimeZone.getTimeZone("UTC"));

        // Register custom JPQL generators. Called here just so that we're sure they will be registered once
        JPQLGeneratorRegister.registerCustomJPQLGenerators();

        return new Elide(builder.build());
    }

NB: The above code will only work for JSON objects that are directly within the parent object (e.g. item ->> itemCode). Fields within inner objects will not work (e.g. item ->> itemDimensions ->> width). I'll have to modify this code to achieve this, but since I don't have such a use case at the moment, I will not do it.

Let me know if I could have done this is a better way

[thaingo]

@lewimuchiri I tried your implementation but it does not work as expected. Could you please advise How your filter looks like in this case from the client view?

[lewimuchiri]

@thaingo Here is a full example based on the configuration I gave in my previous comments:

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class StockTakeUser {
    private String login;
    private String name;
}

@Entity
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "stock_list")
@Include
@DeletePermission(expression = Role.NONE_ROLE)
@UpdatePermission(expression = Role.NONE_ROLE)
@CreatePermission(expression = Role.NONE_ROLE)
public class StockList {
     //... other fields here

    @Type(JsonType.class)
    @Column(columnDefinition = "JSON")
    private StockTakeUser counter;
}

And the GraphQL query:

{
  stockList(filter:"counter.login=='Test'") {
    edges {
      node {
        id
        code
        comments
        counter {
          login
          name
        } 
        status
      }
    }
    pageInfo {
      hasNextPage
      startCursor
      endCursor
      totalRecords
    }
  }
}

After running the request, the generated query was:

    select
        s1_0.id,
        s1_0.code,
        s1_0.comments,
        s1_0.counter
    from
        stock_list s1_0 
    where
        json_extract_path_text(s1_0.counter,'login')=? offset ? rows fetch first ? rows only

[thaingo]

Great and thanks @lewimuchiri for your quick reply.

Curious question: what would be change to your implementation to support a List<StockTakeUser> instead?

[lewimuchiri]

When you open EntityDictionary.java within the Elide library sources you will find a method called bindEntity(). Within it you will find another method called discoverEmbeddedTypeBindings() which is called therein. This is where the entity bindings are being done. The problem with List<> attributes is that the type is List which is a collection and so the enclosed object type will not be included in the entity bindings (see below screenshot for where the check is being done). That is why when you try to write a filter on a List, you will get an exception Unbound Entity ClassType....

I wonder though, is it possible to filter by json field if that field is an array? Looks like something that can be done via jsonpath. Not sure if you can do that directly via SQL.

[thaingo]

Thank you once again for your reply and really appreciate your time.

I was in that code segment this whole morning when I faced the exception Unbound Entity ClassType wanted to check if I missed anything from your Implementation.

So I guess we both need more help. @aklish Could you advise us on this matter?