How do I query a JSON datatype from postgresql with clojure.java.jdbc?

Question

I understand how to:

But I don't know how to put it together and query json with clojure.java.jdbc.

For example, I have a table user, and a field user_info

CREATE TABLE user (id SERIAL,user_info JSON);

then I found this blog to impl some protocol,and it insert success!

(insert! conn :yxt_user {:user_info {:hello [1 "test" true]}})

But I don't know how to write code to query it like this sql from jdbc/query

SELECT * FROM user WHERE data ? 'hello';

not by jdbc/execute direct write sql like

(jdbc/execute! db-spec ["UPDATE table SET col1 = NOW() WHERE id = ?" 77])

I tried to write this query

(jdbc/query conn ["SELECT * FROM user WHERE user_info ? 'hello'"])

I got this

org.postgresql.util.PSQLException: 未设定参数值 1 的内容。

Then I tried

(jdbc/query conn ["SELECT * FROM user WHERE user_info ? 'hello'" "?"])

I got this

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

How do I write a query to filter on a JSON column where user_info has the JSON key hello?

Can you please describe, both for the select and the update, the code you are trying to run and the error message you get? — bsvingen
– bsvingen, Commented May 7, 2015 at 16:27
because some error message is chinese,i am afraid you don't understand. — ipaomian
– ipaomian, Commented May 7, 2015 at 16:57
What is userinfo ? 'hello' supposed to do? Do you mean userinfo = 'hello'? In the JSON field? — bsvingen
– bsvingen, Commented May 7, 2015 at 17:13
no,it is postgresql json query grammar,it means all json which has key hello schinckel.net/2014/05/25/querying-json-in-postgres — ipaomian
– ipaomian, Commented May 7, 2015 at 17:35
This is, by the way, very close to (if not a duplicate of) stackoverflow.com/questions/14779896/… — Charles Duffy
– Charles Duffy, Commented May 7, 2015 at 22:07

Sherman · Accepted Answer · 2015-05-29 18:19:15Z

2

If you have the latest postgresql driver you can escape the ? with a double ??

This should work:

 (jdbc/query conn ["SELECT * FROM user WHERE user_info ?? 'hello'"])

answered May 29, 2015 at 18:19

Sherman

9079 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1