Spring JPA:想要在JSON列中添加键值对
我正在将 spring jpa(spring-data-jpa-1.11.4) 与 mysql Connector-5 一起使用。 在 mysql 中,我有一个名为 chat_templates 的表,其中包含 json 类型的 cta_data 列。
用例:我想在 mysql 列的现有 json 中添加一个新的键值对。
观察:我在 mysql cli 上尝试过 mysql 本机查询,它运行良好,并且我能够在 json 列中添加新的键值对。
更新 chat_templates set cta_data=JSON_INSERT(cta_data,'$.new_test_key',23232) 其中 id=777;
mysql>从 chat_templates 中选择 cta_data,其中 id =777\G; ************************** 1. 行 ****************** ****** cta_data: {"?1": "jjjjjj", "dsd": 23232, "ioi": "jjjjjj", "klm": 1, "aaaa": "kkkkksds", "cta1": {"link": " paytmmp://fastag", "text": "上传文档"}, "cta2": null, “jjkjj”:11111,“rttrt”:577,“hhuugu”:11111,“klm111”:1,“nnnnnn”:11111,“new_test_key”:23232} 一组 1 行(0.00 秒)
但是当我在 spring jpa 上使用类似的查询时。它不起作用。给我错误
<前><代码>@修改 @事务性 @Query(value = "更新chat_templates c set c.cta_data = JSON_INSERT(c.cta_data, '$.?1', ?1) where c.id=777", nativeQuery = true) 无效测试(int k);
错误日志:
JSON 路径表达式无效。错误发生在字符位置 4 附近。
构造函数抛出异常;嵌套异常是org.springframework.dao.InvalidDataAccessResourceUsageException:无法执行语句; SQL [不适用];嵌套异常是org.hibernate.exception.SQLGrammarException:无法执行语句
然后我对查询进行了一些更改(删除了单引号),仍然收到另一种类型的错误。
<前><代码>@修改 @事务性 @Query(value = "更新chat_templates c set c.cta_data = JSON_INSERT(c.cta_data, $.?1, ?1) where c.id=777", nativeQuery = true) 无效测试(int k);
错误日志:
“字段列表”中存在未知列“$.new_test_key”(new_test_key 是动态值)
构造函数抛出异常;嵌套异常是org.springframework.dao.InvalidDataAccessResourceUsageException:无法执行语句; SQL [不适用];嵌套异常是org.hibernate.exception.SQLGrammarException:无法执行语句
如果我错过了任何内容,请提出建议。
提前致谢..!!
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..!!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论