Postgre数据库Jsonb字段json条件过滤

IT-Lenjor

已于 2022-09-23 13:56:32 修改

阅读量1.6k

点赞数

分类专栏： Mysql 文章标签：数据库

于 2021-12-27 14:41:34 首次发布

本文链接：https://blog.csdn.net/weixin_29053561/article/details/122171729

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

JSON列表过滤

{
    "threshold": [
        {
            "end_time": null,
            "end_period": null,
            "start_time": null,
            "start_period": null,
            "low_threshold": null,
            "high_threshold": null,
            "threshold_type": "GLOBAL",
            "too_low_threshold": -2,
            "too_high_threshold": 9
        },
        {
            "end_time": "09-30  23:59:59",
            "end_period": "23:59:59",
            "start_time": "06-01  00:00:00",
            "start_period": "00:00:00",
            "low_threshold": null,
            "high_threshold": null,
            "threshold_type": "TIMING",
            "too_low_threshold": 5,
            "too_high_threshold": 8
        },
        {
            "end_time": "05-03  23:59:59",
            "end_period": "09:58:59",
            "start_time": "01-01  00:00:00",
            "start_period": "00:00:00",
            "low_threshold": 2,
            "high_threshold": 3,
            "threshold_type": "TIMING",
            "too_low_threshold": 1,
            "too_high_threshold": 5
        },
        {
            "end_time": "05-03  23:59:59",
            "end_period": "23:59:59",
            "start_time": "01-01  00:00:00",
            "start_period": "09:59:00",
            "low_threshold": null,
            "high_threshold": null,
            "threshold_type": "TIMING",
            "too_low_threshold": 2,
            "too_high_threshold": 6
        },
        {
            "end_time": "12-31  23:59:59",
            "end_period": "23:59:59",
            "start_time": "10-01  00:00:00",
            "start_period": "00:00:00",
            "low_threshold": null,
            "high_threshold": null,
            "threshold_type": "TIMING",
            "too_low_threshold": 1,
            "too_high_threshold": 7
        }
    ]
}

SELECT * from idp_measure_point where alarm_config_json is not null and json_extract_path(alarm_config_json::json#>'{threshold, 1}', 'threshold_type')::text='"TIMING"' ORDER BY create_time desc;

Mybatis 里SQL写法，json字段过滤

 <select id="selectPushDataList" resultMap="BaseResultMap" parameterType="com.jpc.JpushData" >
    SELECT
      <include refid="Base_Column_List" />
    FROM
        jp_push jpt
    <where>
      delete_flag=1
      and (jpt.rule ->> 'sendType' )::text = 'tag'
      <if test="name != null and name !=''" >
        and name =#{name,jdbcType=VARCHAR}
      </if>
      ORDER BY
        create_time ASC
         LIMIT ${pageSize} OFFSET ${(currentPage - 1) * pageSize}
    </where>
  </select>