如何在postgres查询中转义::jsonb?

e4yzc0pl  于 2021-07-03  发布在  Java
关注(0)|答案(1)|浏览(373)

执行查询时出现以下异常
org.hibernate.queryexception:未设置所有命名参数:
[:jsonb]
我的问题是:

SELECT * FROM content c,resource r,organization_resource_relationship 
org,json_array_elements(c.attributes->'data') obj
WHERE c.resource_id=r.resource_id and r.account_id='633' and    
r.resource_id = org.resource_id and r.delete_status = false 
and ((obj->>'userId' in(select cast(resource_id as text) from resource 
 where resource_id=3181)  or  obj->>'signatureByCmisUsers'
 like '%3181%' or  obj->>'visibleToCmisUsers' like '%3181%' or  
 obj->>'editableByCmisUsers' like '%3181%' or  
 obj->>'obtainSignatureByCmisUsers' 
 like '%3181%' or  obj->>'documentVisibleToCmisUsers' like '%3181%' ) and 
  (obj->>'signatureStatus' like 'Pending'  or obj->>'signatureStatus'
  like 'PENDING'  or obj->>'signatureStatus' like 'SIGNED' or 
  obj->>'signatureStatus' like 'Signed') and   
 (obj->>'isDeleted'='false') 
   and ((obj->>'parentFolderId'='nil' or obj->>'parentFolderId' in 
     (select cast(resource_id as text) from
    resource re where re.delete_status=false))) and 
    ((obj->>'userType'='owner' or obj->>'userType'='receiver'))) 
    and ((((obj->>'visibleToRoles' like '%215%') and  
    ((obj->>'visibleToRoles') ::jsonb) ? 
    (select cast(resource_to_id as text) from resource_relationship where 
     resource_from_id=3181 and resource_to_id=215))))  
     and json_typeof(c.attributes->'data')='array' ORDER BY r.updated_on 
      DESC

如何解决这个问题?我需要使用 ::jsonb . 但它抛出了一个例外。

9lowa7mx

9lowa7mx1#

使用位置参数 ?1 , ?2 等等。hibernate在遇到 : ,也没有“逃避”,因为这是合法的postgres铸造速记。
你也可以用 CAST(obj->>'visibleToRoles' AS jsonb) ,但如果您喜欢速记语法,则不能使用命名参数。
实际上,你为什么要投?为什么不带上它呢 -> 而不是 ->> ?
顺便说一句,这个查询很好(这是讽刺,看起来很可怕,效率很低)。

相关问题