2022-02-11 hugsql | Clojure Slack Archive

hugsql

Victor 2022-02-11T14:04:02.402629Z

Hi everyone. I’m fairly new to clojure and am trying to use a JSONB ?| operator with hugsql. What I would like to do is pass a vector through as a param. I’m seeing this error and I’m not sure how to use the vector appropriately in vector.

actual: org.postgresql.util.PSQLException: ERROR: operator does not exist: text < jsonb
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Thanks for any help in advance!

(sql/read-all-with-options tx {:org_id "org_test" :exclude_roles ["admin" "org_admin"]})

-- :name read-all-with-options :? :*
-- :doc get all users and filter out excluded roles
select * from users where org_id = :org_id
and (allowed_roles ??| ARRAY[ :exclude_roles ]) is false

lukasz 2022-02-11T15:36:22.705069Z

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

Victor 2022-02-11T15:41:43.098829Z

Hey lukasz, unfortunately that didn’t work either.

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

lukasz 2022-02-11T15:47:40.707729Z

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

Victor 2022-02-11T15:48:00.108019Z

thank you so much!

Victor 2022-02-11T15:56:53.096769Z

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)})

lukasz 2022-02-11T15:57:18.489139Z

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

lukasz 2022-02-11T15:57:56.388339Z

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

lukasz 2022-02-11T15:58:35.347479Z

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

lukasz 2022-02-11T15:59:51.847329Z

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"])})

Victor 2022-02-11T16:00:27.975509Z

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

lukasz 2022-02-11T16:03:00.109749Z

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

Victor 2022-02-11T16:08:08.245689Z

thank you man! that worked perfectly

Victor 2022-02-11T16:08:15.121929Z

appreciate the help 🙂

lukasz 2022-02-11T16:16:07.328729Z

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)

😁 1

🙌 1

Clojurians Log v2

hugsql