Can't verify but wouldn't :exclude_roles::array work here instead?

Hey lukasz, unfortunately that didn’t work either.

actual: org.postgresql.util.PSQLException: ERROR: syntax error at or near "array"

Let me check - I believe one of our production services does a similar query

thank you so much!

I tried logging what :exclude_roles is being passed as to the sql query by doing SELECT (:exclude_roles); This is the response. Not sure if this is helpful:

({:?column? (apply_applicant ob_anon_primary)})

Ok, we didn't quite have the same query, but part of our system deals with typed arrays and jsonb so here's how you make it work:

select * from json_test where store->'foo' ??| :ids 

and in your Clojure code you do: (query-ns/hugsql-query db {:ids (into-array ["qux"])})

where the table is defined as:

nomnom=# select * from json_test;
          store
-------------------------
 {"foo": ["abc", "def"]}
 {"foo": ["qux", "def"]}
 {"foo": ["abc", "zxc"]}
 {"foo": ["qux", "zxc"]}
(4 rows)

nomnom=# \d json_test ;
              Table "public.json_test"
 Column | Type  | Collation | Nullable |   Default
--------+-------+-----------+----------+-------------
 store  | jsonb |           |          | '{}'::jsonb

So, in your example, you'd change ARRAY[:exclude_roles] to just :exclude_roles and when calling your query you'd write it as: (sql/read-all-with-options tx {:org_id "org_test" :exclude_roles (into-array ["admin" "org_admin"])})

Thanks so much, sorry I’m new to clojure, where do I put this? (query-ns/hugsql-query db {:ids (into-array ["qux"])})

Hopefully this is more readable : https://gist.github.com/lukaszkorecki/25cfa180b421fe269617b062463008fb

thank you man! that worked perfectly

appreciate the help 🙂

np, JSONB and PG arrays are one of the sharper edges in HugSQL - you can make it all work, but some bits are not obvious (like type casting in the query itself vs what type is passed to the PG driver)