Spring JPA : Want to add key-value pair in json column

Question

I am using spring jpa(spring-data-jpa-1.11.4) with mysql connector-5. In mysql, I have a table named chat_templates with column cta_data of json type.

Use Case :I want to add a new key-value pair in existing json of mysql column.

Observations: I have tried mysql native query on mysql cli which is working fine and i am able to add new key-value pair in json column.

update chat_templates set cta_data=JSON_INSERT(cta_data,'$.new_test_key',23232) where id=777;

mysql> select cta_data from chat_templates where id =777\G; *************************** 1. row *************************** cta_data: {"?1": "jjjjjj", "dsd": 23232, "ioi": "jjjjjj", "klm": 1, "aaaa": "kkkkksds", "cta1": {"link": "paytmmp://fastag", "text": "Upload Documents"}, "cta2": null, "jjkjj": 11111, "rttrt": 577, "hhuugu": 11111, "klm111": 1, "nnnnnn": 11111, "new_test_key": 23232} 1 row in set (0.00 sec)

But when i am using similar query on spring jpa. It is not working. Giving me error

@Modifying
@Transactional
@Query(value = "update chat_templates c set c.cta_data = JSON_INSERT(c.cta_data, '$.?1', ?1)  where c.id=777", nativeQuery = true)
void test(int  k);

Error logs :

Invalid JSON path expression. The error is around character position 4.

Constructor threw exception; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

Then i have made some changes(removed single quotes) on query, still getting another type of error.

@Modifying
@Transactional
@Query(value = "update chat_templates c set c.cta_data = JSON_INSERT(c.cta_data, $.?1, ?1)  where c.id=777", nativeQuery = true)
void test(int  k);

Error logs :

Unknown column '$.new_test_key' in 'field list' (new_test_key is the dynamic value)

Constructor threw exception; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

Kindly suggest if i have missed anything.

Thanks in advance..!!