How to filter by nested and array JSON(JSONB) node?

[voronovmaksim]

I have jsonB column "config"

{
  "simpleNode": "simpleNodeValue",
  "nestedNode": {
    "nestedKey": "nestedKeyValue"
  },
  "arrayNode":[
    {
      "arrayNodeKey": "arrayNodeValue"
    }
  ]
}

Entity:

@Entity
@Table(name = "MyEntity")
public class MyEntity {
    @Column(columnDefinition = "jsonb")
    @JsonProperty("config")
    @JdbcTypeCode(SqlTypes.JSON)
    Config config;
}

public class Config {
    @JsonbProperty("simpleNode")
    @JsonProperty("simpleNode")
    String simpleNode;

    @JsonbProperty("nestedNode")
    @JsonProperty("nestedNode")
    NestedNode nestedNode;

    @JsonbProperty("arrayNode")
    @JsonProperty("arrayNode")
    List<ArrayNode> arrayNode;
}

public class NestedNode {
    @JsonProperty("nestedKey")
    @JsonbProperty("nestedKey")
    String nestedKey;
}

public class ArrayNode {
    @JsonProperty("arrayNodeKey")
    @JsonbProperty("arrayNodeKey")
    String arrayNodeKey;
}

i want to filter by the nodes.

For simpleNode it works fine

 String whereExpression = "JSON_GET(config, 'simpleNode')";
 RestrictionBuilder<? extends CriteriaBuilder<?>> where = criteriaBuilder.where(whereExpression);
 where.eq("simpleNodeValue");

How to filter by nestedNode(nestedKey) and ArrayNode(arrayNodeKey)?

I've tried for nestedKey:

            whereExpression = "JSON_GET(config, 'nestedNode.nestedKey')";
            whereExpression = "JSON_GET(config.nestedNode, 'nestedKey')";
            whereExpression = "JSON_GET(config, `nestedNode`, 'nestedKey')";
            whereExpression = "JSON_GET(config, `config`, `nestedNode`, 'nestedKey')";

It doesn't work. For array it also doesn't work.

[beikov]

Accessing nested state works by specifying sub-selectors as arguments i.e. JSON_GET(config, 'nestedNode', 'nestedKey') will access the nested state. When you want to access arrays, you will have to either specify the array index e.g. JSON_GET(config, 'arrayNode', '0', 'arrayNodeKey').

[voronovmaksim]

Hi @beikov, thank you very much for spotting and fixing my mistake—I really appreciate it! Everything works.

I have a question about using wildcards in array nodes without a hardcoded index. What if I want to find all records where config.arrayNode[*].arrayNodeKey = arrayNodeValue, regardless of the node index?

It seems that JSON_GET(config, 'arrayNode', '*', 'arrayNodeKey') is not supported.

What should I do in this case? Should I write my own custom JPQL functions, or is there an existing solution for this?

[beikov]

What should I do in this case? Should I write my own custom JPQL functions, or is there an existing solution for this?

For now, you will have to do that, yes, or you build Blaze-Persistence from source since we recently added support for this via https://github.com/Blazebit/blaze-persistence/issues/1928.

[voronovmaksim]

I am very lucky that you've implemented it recently. I will wait for the official release to get the feature instead of writing my own workaround code. Thanks a lot!