postgre数据库中json及jsonb字段相关操作及函数

1.获取jsonb字段中所有key，以及key对应的去重值集合

	SELECT key, array_agg(DISTINCT value #>> '{}') AS values
    FROM (
      SELECT t.key,t.value
      FROM form_main_data
      CROSS JOIN json_each(data) AS t(key, value)
      WHERE json_typeof(t.value) IN ('string', 'number')
      ) sub
    GROUP BY key
    ORDER BY key;

2.根据jsonb字段的key，以及key对应的value查询，Java Mapper接口定义参数为【Map<String, Object> conditionMap】

<select id="filterByConditions" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"/>
    from form_main_data
    where form_segment_info_id = #{segmentId,jdbcType=INTEGER}
    <foreach collection="conditionMap.entrySet()" item="entry" separator="and">
      <choose>
        <!-- 数组多值筛选 -->
        <when test="entry.value instanceof java.util.List">
          <trim prefix="(" suffix=")" suffixOverrides="OR">
            <foreach collection="entry.value" item="item" open="(" separator="," close=")">
              <choose>
                <when test="item instanceof java.lang.Integer or item instanceof java.lang.Long or item instanceof java.math.BigDecimal">
                  (form_response->> #{entry.key})::numeric = #{item} OR
                </when>
                <when test="columnValue instanceof java.lang.Boolean">
                  (form_response->> #{entry.key})::boolean = #{item} OR
                </when>
                <otherwise>
                  form_response->> #{entry.key} = #{item} OR
                </otherwise>
              </choose>
            </foreach>
          </trim>
        </when>

        <!-- 单项筛选 -->
        <otherwise>
          <choose>
            <when test="entry.value instanceof java.lang.Integer or entry.value instanceof java.lang.Long or entry.value instanceof java.math.BigDecimal">
              (form_response->> #{entry.key})::numeric = #{entry.value}
            </when>
            <when test="columnValue instanceof java.lang.Boolean">
              (form_response->> #{entry.key})::boolean = #{entry.value}
            </when>
            <otherwise>
              form_response->> #{entry.key} = #{entry.value}
            </otherwise>
          </choose>
        </otherwise>
      </choose>
    </foreach>
  </select>

3.两张表join，A表有就返回A表数据，B表有就返回B表数据：coalesce()函数，相对于单纯join以及内存操作速度提升非常明显

select
      coalesce(vtsr.form_id, res.form_id) as form_id,
      coalesce(vtsr.form_type, res.form_type) as form_type,
      coalesce(vtsr.form_name, res.form_name) as form_name,
      coalesce(vtsr.form_description, res.form_description) as form_description,
      coalesce(vtsr.remind_info, res.remind_info) as remind_info,
      coalesce(vtsr.form_status, res.form_status) as form_status,
      coalesce(vtsr.form_publish_time, res.form_publish_time) as form_publish_time,
      coalesce(vtsr.form_close_time, res.form_close_time) as form_close_time,
      coalesce(vtsr.form_subscriber_no, (res.data->>'经销商代码*')::numeric) as form_subscriber_no,
      coalesce(vtsr.form_segment_type, res.form_segment_type) as form_segment_type,
      COALESCE(vtsr.form_response, res.data::jsonb) as form_response,
      coalesce(vtsr.response_id, 0) as response_id
    from (select
            fmd.id as id,
            fbi.id as form_id,
            fbi.type as form_type,
            fbi.name as form_name,
            fbi.description as form_description,
            fbi.remind_settings as remind_info,
            fbi.status as form_status,
            fbi.publish_time as form_publish_time,
            fbi.close_time as form_close_time,
            fsi.type as form_segment_type,
            fmd.data as data
          from form_main_data fmd
                 left join form_segment_info fsi on fmd.form_segment_info_id = fsi.id
                 left join form_basic_info fbi on fsi.form_id = fbi.id
          where fmd.form_segment_info_id = #{segmentId,jdbcType=INTEGER}) res left join vw_tfs_subscriber_response vtsr on (res.id)::text = (vtsr.form_response->>'id')::text

4.json字段与jsonb字段的函数基本可以通用，例如jsonb_each是jsonb字段的函数，那么json_each就是json字段的函数，去掉一个b就可以用，目前我用到的几个函数是这样的，不保证全部的都可以这么用。