Pgsql中数组字段当作查询条件和输出数组

1、博客中关于 PostgreSql Array操作的操作符和函数

https://my.oschina.net/Kenyon/blog/133974

2、官方文档：

https://www.postgresql.org/docs/9.2/functions-array.html

3、pgsql中的窗口函数

https://blog.youkuaiyun.com/xfg0218/article/details/104340898

–最后一句，提示array_agg() 在9.20 .将结果集转成数组的 array_agg()

4、关于自己的sql

select dev.name as devName,rel.id,rel.plan_id,rel.dev_id,rel.spare_ids ,ARRAY_AGG(coalesce(spare.name,''))  as spareName
from rs_maintenance_plan_dev_rel rel 
left join rs_dev_info dev on dev.id=rel.dev_id
left join rs_dev_spare_part_info spare on 

string_to_array(spare.id,',') && rel.spare_ids::text[]

where rel.plan_id='832575251038732288' 

group by dev.name ,rel.id,rel.plan_id,rel.dev_id,rel.spare_ids

– 结尾，最后的结果不是想要的，空的效果。{}，没加coalesce之前是{NULL}

上述sql在java-mybaties中出现问题了

&& 解析成了 and

### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type text[]
  位置：296
### The error may exist in file [E:\etcom\work5\basic-web\target\classes\mapper\maintenance\RsMaintenancePlanDevRelMapper.xml]
### The error may involve cn.etcom.web.dao.resource.maintenance.RsMaintenancePlanDevRelMapper.selectList2-Inline
### The error occurred while setting parameters
### SQL: SELECT dev.name AS devName, rel.id, rel.plan_id, rel.dev_id, rel.spare_ids, ARRAY_AGG(coalesce(spare.name, '')) AS spareNames FROM rs_maintenance_plan_dev_rel rel LEFT JOIN rs_dev_info dev ON dev.id = rel.dev_id AND dev.tenant_id = '682230092146921472' LEFT JOIN rs_dev_spare_part_info spare ON string_to_array(spare.id, ',') AND rel.spare_ids::text[] AND spare.tenant_id = '682230092146921472' WHERE rel.plan_id = ? AND rel.tenant_id = '682230092146921472' GROUP BY dev.name, rel.id, rel.plan_id, rel.dev_id, rel.spare_ids
### Cause: org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type text[]
  位置：296
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type text[]
  位置：296] with root cause
org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type text[]
  位置：296
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2505) ~[postgresql-42.2.9.jar:42.2.9]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2241) ~[postgresql-42.2.9.jar:42.2.9]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310) ~[postgresql-42.2.9.jar:42.2.9]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447) ~[postgresql-42.2.9.jar:42.2.9]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368) ~[postgresql-42.2.9.jar:42.2.9]
	

最后将 && 有交集就t 的改成包含于 <@
即：
‘& l t;@’ 最后mybatis中的sql：

<select id="selectList2" parameterType="java.lang.String" resultMap="LinkResultMap">
    select dev.name as devName,rel.id,rel.plan_id,rel.dev_id,rel.spare_ids ,ARRAY_AGG(coalesce(spare.name,''))  as spareNames
    from rs_maintenance_plan_dev_rel rel
           left join rs_dev_info dev on dev.id=rel.dev_id
           left join rs_dev_spare_part_info spare on

      string_to_array(spare.id,',') /*<![CDATA[&& ]]>*/ &lt;@ rel.spare_ids::text[]

    where rel.plan_id=#{planId}

    group by dev.name ,rel.id,rel.plan_id,rel.dev_id,rel.spare_ids
  </select>

5、项目其他地方的应用，数据是数组字段的地方

• 先说引用的mybatis-plus的版本3.3.0，数据库是pgsql，pom.xml配置如下：

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.0</version>
</dependency>

• 实体类中的属性如下：

/**
 * 设备图片
 */
@TableField(value = "icon",typeHandler = ArrayTypeHandler.class)
@ApiModelProperty(value = "设备图片")
private String[] icon;

• XXmapper.xml中的resultMap中的内容如下：

<result column="icon" jdbcType="ARRAY" property="icon" typeHandler="org.apache.ibatis.type.ArrayTypeHandler" />

• 其他使用数组当成查询条件的如下：

<select id="selectPage2" resultMap="BaseResultMap2">

    select
    <include refid="Base_Column_List2"></include>
    from rs_dev_info info
        left join rs_dev_type_info typeinfo on info.dev_type_id = typeinfo.id
    left join rs_dev_supplier_info sup on info.supplier = sup.id
    left join rs_dev_supplier_info sup2 on info.producer = sup2.id
        left join rs_area_info area on info.area_id = area.id
        left join sys_dept_info dept on info.dept_id = dept.id
    where 1=1
    <if test="rsDevInfo.name != null and rsDevInfo.name != ''">
      <bind name="nameLike" value="'%' + rsDevInfo.name + '%'"/>
      and info.name like #{nameLike,jdbcType=VARCHAR}
    </if>
    <if test="rsDevInfo.code != null and rsDevInfo.code != ''">
      <bind name="codeLike" value="'%' + rsDevInfo.code + '%'"/>
      and info.code like #{codeLike,jdbcType=VARCHAR}
    </if>
    <if test="rsDevInfo.devTypeId != null and rsDevInfo.devTypeId != ''">
      <bind name="devTypeId" value="rsDevInfo.devTypeId"/>
      and info.dev_type_id = #{devTypeId}
    </if>
    <if test="rsDevInfo.deviceAddr != null and rsDevInfo.deviceAddr != ''">
      <bind name="deviceAddr" value="'%' + rsDevInfo.deviceAddr + '%'"/>
      and info.device_addr like #{deviceAddr,jdbcType=VARCHAR}
    </if>
    <if test="rsDevInfo.areaId != null and rsDevInfo.areaId != ''">
      <bind name="areaId" value="rsDevInfo.areaId"/>
      and info.area_id = #{areaId}
    </if>
    <if test="rsDevInfo.deptId != null and rsDevInfo.deptId != ''">
      <bind name="deptId" value="rsDevInfo.deptId"/>
      and info.dept_id = #{deptId}
    </if>
    <if test="rsDevInfo.supplier != null and rsDevInfo.supplier != ''">
      <bind name="supplier" value="rsDevInfo.supplier"/>
      and info.supplier = #{supplier}
    </if>
    <if test="rsDevInfo.runStatus != null and rsDevInfo.runStatus != ''">
      <bind name="runStatus" value="rsDevInfo.runStatus"/>
      and info.run_status = #{runStatus}
    </if>
    <if test="rsDevInfo.devIds != null">
      and info.id not in (
      <foreach collection="rsDevInfo.devIds" item="devId" separator=",">
        #{devId}
      </foreach>
      )
    </if>
    <if test="rsDevInfo.devNo != null and rsDevInfo.devNo != ''">
      <bind name="devNo" value="rsDevInfo.devNo"/>
      and info.dev_no = #{devNo}
    </if>
    and   (string_to_array(info.area_id,',') &lt;@  (select aread_id from rs_user_area_rel where user_no=#{rsDevInfo.opUser})::text[])
    and   (string_to_array(info.dev_type_id,',') &lt;@  (select dev_type_id from rs_user_area_rel where user_no=#{rsDevInfo.opUser})::text[])

    order by info.name asc
  </select>

string_to_array 就是讲单个字段列，转成数组。