1

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?

Community
  • 1
  • 1
ipaomian
  • 319
  • 2
  • 12
  • 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 May 07 '15 at 16:27
  • because some error message is chinese,i am afraid you don't understand. – ipaomian May 07 '15 at 16:57
  • What is `userinfo ? 'hello'` supposed to do? Do you mean `userinfo = 'hello'`? In the JSON field? – bsvingen May 07 '15 at 17:13
  • no,it is postgresql json query grammar,it means all json which has key `hello` http://schinckel.net/2014/05/25/querying-json-in-postgres/ – ipaomian May 07 '15 at 17:35
  • So then it's probably confusing the first '?' with '?' as a placeholder? I don't believe you can use placeholders to insert operators, which might be what you are trying to do. Have you tried escaping the '?'? – bsvingen May 07 '15 at 17:44
  • I'd suggest asking this as a JDBC question alone, with the Clojure aspects filtered out, to get the widest range of responses. – Charles Duffy May 07 '15 at 22:05
  • 3
    This is, by the way, very close to (if not a duplicate of) http://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes – Charles Duffy May 07 '15 at 22:07
  • Personally, I would consider writing the query to call `json_object_keys()` and filter on its output -- if you don't care about performance -- or creating a stored procedure that encapsulates the syntax you need -- if you do. – Charles Duffy May 07 '15 at 22:45
  • yes，I think it is very http://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes it is my wey of search is stubborn.can't find that question.thanks – ipaomian May 08 '15 at 03:36
  • I am not sure though if they already introduced a workaround in the JDBC driver. – Mark Rotteveel May 08 '15 at 08:16

1 Answers1

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'"])
Sherman
  • 827
  • 8
  • 16