SELECT JSON_VALUE(CAST(detail_json AS JSONB), '$.guid') AS guid
FROM integrated_gov_platform_info;
  • 1.
  • 2.

querywrapper

List<GovPlatformInfo> govPlatformInfos = list(new QueryWrapper<GovPlatformInfo>()
                    .apply("JSON_VALUE(CAST(detail_json AS JSONB), '$.guid') = '{0}'", dtPracticeParam.getGuid()));//提取detail_json 里面的guid 进行比较
  • 1.
  • 2.

修正querywrapper

return dtPracticeParams.stream()
                .allMatch(dtPracticeParam -> {
                    List<String> guids = this.baseMapper.getGuidsByParams(dtPracticeParam.getQcert(), itemid);
                    return guids.contains(dtPracticeParam.getGuid());
                });
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="vip.xiaonuo.modular.govplatform.mapper.GovPlatformInfoMapper">
<!--    查询json里面的guid -->
    <select id="getGuidsByParams" resultType="java.lang.String">
        SELECT JSON_VALUE(CAST(detail_json AS JSONB), '$.guid') AS guid
        FROM integrated_gov_platform_info
        WHERE status = 0 AND apply_idcard = #{applyIdcard} AND itemid = #{itemid}
    </select>
</mapper>
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

多条json 数据

SELECT JSON_VALUE(CAST(json_element AS JSONB), '$.guid') AS guid
        FROM (
                 SELECT CAST(detail_json AS JSONB),
                        JSONB_ARRAY_ELEMENTS(CAST(detail_json AS JSONB)) AS json_element
                 FROM integrated_gov_platform_info
                 WHERE status = 0
                   AND apply_idcard = #{applyIdcard}
                   AND itemid = #{itemid}
             )
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.