How can i escape the ::JSONB in postgres query?

Question

I am getting following exception while executing the query

org.hibernate.QueryException: Not all named parameters have been set:
[:jsonb]

This is my query:

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

How can solve the issue? I need to use ::jsonb. But it throws an exception.

Kayaman · Answer 1 · 2017-09-05T06:04:42.103

0

Use positional parameters ?1, ?2 etc. instead. Hibernate gets confused when it encounters :, and there's no "escape" as it's legal Postgres shorthand for casting.

You can also replace it with CAST(obj->>'visibleToRoles' AS jsonb), but if you prefer the shorthand syntax you can't use named parameters.

Actually, why are you even casting it? Why not get it with -> instead of ->>?

Nice query btw. (And that's sarcasm, it looks horrible and inefficient).

edited Sep 05 '17 at 06:04

answered Sep 05 '17 at 05:57

Kayaman

72,141
5
83
121

Thank You Kayaman , Hi That error fixed , But now showing Expected positional parameter count: 1, actual parameters: [] . I have used ? in previous query that you can see after jsonb . But not in purpose of positional parameter . How can i fix this ? Please help – PRASANTHMV Sep 05 '17 at 06:19
Heh, then I'd guess you should use `CAST` instead, or `->` if you intend to interpret it as JSON anyway. – Kayaman Sep 05 '17 at 06:27
cast(obj->'visibleToRoles' as jsonb) ?" + " (select cast(resource_to_id as text) from resource_relationship where resource_from_id=3181 and resource_to_id=215)))) is query correct now ? But this also showing that error . Could you send me these part modification ? – PRASANTHMV Sep 05 '17 at 06:52
Based on https://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes it seems you could probably use `??` to get around that. – Kayaman Sep 05 '17 at 06:55
Hi this I tried . But Expected positional parameter count: 2, actual parameters: [] error showing . Any other solution . Someway to escape that character – PRASANTHMV Sep 05 '17 at 07:00
I have got another query and i replace some part of above query : (select cast(resource_to_id as text) from resource_relationship where resource_from_id=3181 and resource_to_id=215)=ANY(ARRAY(select * from json_array_elements(obj->'visibleToRoles'))::text[]) . But then comes same error of jsonb .. How can i solve this . ? – PRASANTHMV Sep 05 '17 at 08:38

How can i escape the ::JSONB in postgres query?

1 Answers1