JPA native query on JSON column with dynamic key & value

I try to build a query dynamically using JPA and native query. The query concerns a table which contains a JSON string in a column. My column contains the following data:

{
    "key1": "value1", 
    "key2": "value2", 
    "key3": "value3", 
}

In mysql my query would look like this :

SELECT 
    id, 
    JSON_UNQUOTE(jsonstr-> '$.key1') AS key1,
    columns
FROM jsondata WHERE jsonstr -> '$.key1' = "value1";

I want to query on any property key and check the value at this stage. For this, I created a JsonDataEntity:

@Entity
@Table(name = "jsondata")
@Getter @Setter
@AllArgsConstructor @NoArgsConstructor
public class Jsondata Entity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Type(JsonType.class)
    @Column(length = Integer.MAX_VALUE, columnDefinition = "json")
    @Convert(converter = HashMapConverter.class)
    private Hashtable<String, Object> jsonstr;
}

the converter allows to tranform String to HashTable and HashTable to JSON string using ObjectMapper.

The reason I use this implementation is that i want to store any json object and want to be able to make queries on each of them, the third reason, is that the application who will use the application knows the key & values for requesting and because i want this part to be reusable.

Now, I created 2 queries for test: in first scenario, i tried to make a basic query on value. I hard coded the key... this query works fine.

@Query(value = "SELECT j.id, j.jsonstr, JSON_UNQUOTE(c.jsonstr-> '$.key1') AS key1 FROM jsondata j " +
        "WHERE j.jsonstr-> '$.key1' = :value", nativeQuery = true)
List<Map<String, Object>> findAllByKeyValue(@Param("value") String value);

Second query would allow me to pass the key i search and the value. I did try to write this but unsuccessfully:

@Query(value = "SELECT j.id, j.jsonstr, JSON_UNQUOTE(j.jsonstr-> '$." + ":keyName"+ "') AS :keyName FROM jsondata j WHERE j.jsonstr -> '$." + ":keyName" + "' = :value", nativeQuery = true)
List<Map<String, Object>> findAllByKeyValues(@Param("keyName") String keyName, @Param("value") String value);

Error message is :

2023-10-31T16:14:23.277+01:00 ERROR 15448 --- [nio-8090-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [SELECT j.id, j.jsonstr, JSON_UNQUOTE(j.jsonstr-> '$.:keyName') AS ? FROM jsondata j WHERE j.jsonstr -> '$.:keyName' = ?] [Invalid JSON path expression. The error is around character position 10.] [n/a]; SQL [n/a]] with root cause

Thanks to Bill Karwin, I finally solved the issue:

@Query(value = "SELECT j.id, j.jsonstr, JSON_UNQUOTE(CONCAT('j.jsonstr-> $.', :keyName)) AS :keyName FROM jsondata c WHERE JSON_EXTRACT(j.jsonstr, CONCAT('$.', :keyName)) = :value", nativeQuery = true)
List<Map<String, Object>> findAllByKeyValues(@Param("keyName") String keyName, @Param("value") String value);

Solution

There are several things I would change.

First, you can't use an expression or a query parameter for a column alias. In this way column aliases follow the same rule as column identifiers. So if you require the column alias to be custom, you have to format that into your SQL query before it is prepared.

You may want to consider if it is really necessary to make a column alias. The alternative is to fetch columns from the result set by ordinal column position instead of by column alias.

Next, the JSON path you are building '$.:keyName' is not valid. You can't put a parameter placeholder inside an SQL string (if you could, how could you make an SQL string of those literal characters?).

So you must format the JSON path by concatenating '$.' with your key name.

There are two ways to do this: in SQL or in Java.

To do it in SQL, you would use MySQL's CONCAT() function. But then you can't use the JSON -> operator, because that doesn't support expressions or function calls. In other words, this is not supported by MySQL:

-- ILLEGAL MySQL SYNTAX
...WHERE j.jsonstr->CONCAT('$.', :keyName) = :value...

So you have to use JSON_EXTRACT():

...WHERE JSON_EXTRACT(j.jsonstr, CONCAT('$.', :keyName)) = :value...

Alternatively you could make the JSON path in Java, then use that as your query parameter.

String jsonPath = "$." + keyName;

But MySQL's -> operator doesn't support a query parameter placeholder either, so you still have to use JSON_EXTRACT():

...WHERE JSON_EXTRACT(j.jsonstr, :jsonPath) = :value...

P.S. I have to comment on this requirement:

i want to store any json object and want to be able to make queries on each of them

This sounds like the Inner-Platform Effect, where you are trying to make a system too "reusable", and you will eventually have to implement a full database management system from scratch using fields of JSON instead of just using normal SQL columns and rows.

It is possible, but it's a lot of work, and you're just duplicating features that the RDBMS already has, but with all new bugs.