mysql 5.7新特新 操作json 数组

select * from  EDI.edi_history where id = 2577;

UPDATE EDI.edi_history SET response_summary = JSON_REPLACE(response_summary, '$[0].isOverMileStone', 'php') WHERE id = 2577;

 

对于json数组,使用$[*]  然后在获取节点.

 

 

 

 

 

@Query(value = "select * from EDI.edi_history where json_contains(response_summary, ?1) and json_contains(response_summary, ?2) and json_contains(response_summary, ?3) and json_contains(response_summary, ?4)", nativeQuery = true)
public List<EdiHistoryDO> findByResponseSummary(String result, String orderNo, String orderId, String docType);

// UPDATE EDI.edi_history SET response_summary = JSON_REPLACE(response_summary,
// '$[0].isOverMileStone', 'true')
// WHERE
// json_contains(response_summary, '{"courierBillNo":"159900000091"}')
// and json_contains(response_summary, '{"result":"SUCCESS"}')
// and response_summary like "%dest_stn%" ;
@Modifying(clearAutomatically = true)
//@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
@Query(value = " UPDATE EDI.edi_history SET response_summary = JSON_REPLACE(response_summary, '$[0].isOverMileStone', ?1) \n "
+ " WHERE \n " + " json_contains(response_summary, ?2)\n "
+ " and json_contains(response_summary, '{\"result\":\"SUCCESS\"}')\n "
+ " and response_summary like \"%dest_stn%\"", nativeQuery = true)
public int updateResponseSummaryIsOverMileStoneByCourierBillNo(String trueOrfalse, String courierBillNoJson);


/**
* get id from ediHistory which include milestone info
*/
@Query(value = " select id from EDI.edi_history "
+ " WHERE \n " + " json_contains(response_summary, ?1)\n "
+ " and json_contains(response_summary, '{\"result\":\"SUCCESS\"}')\n "
+ " and response_summary like \"%dest_stn%\"", nativeQuery = true)
public BigInteger findEdiHistoryId(String courierBillNoJson);