title: How do I query a JSON datatype from postgresql with clojure.java.jdbc?
body: <p>I understand how to:</p>
  
  <ul>
  <li><a href="http://schinckel.net/2014/05/25/querying-json-in-postgres/" rel="nofollow noreferrer">Query JSON in Postgres</a></li>
  <li><a href="https://stackoverflow.com/questions/20655731/how-to-query-data-which-is-json-datatype-from-postgresql-in-sqlalchemy">How to query JSON data from postgresql in sqlalchemy</a></li>
  <li><a href="https://github.com/clojure/java.jdbc" rel="nofollow noreferrer">how to use clojure.java.jdbc</a></li>
  </ul>
  
  <p>But I don't know how to put it together and query json with clojure.java.jdbc.</p>
  
  <p>For example, I have a table user, and a field user_info</p>
  
  <pre><code>CREATE TABLE user (id SERIAL,user_info JSON);
  </code></pre>
  
  <p>then I found <a href="http://www.niwi.be/2014/04/13/postgresql-json-field-with-clojure-and-jdbc/" rel="nofollow noreferrer">this blog</a> to impl some protocol,and it insert success!</p>
  
  <pre><code>(insert! conn :yxt_user {:user_info {:hello [1 "test" true]}})
  </code></pre>
  
  <p>But I don't know how to write code to query it like this sql from jdbc/query</p>
  
  <pre><code>SELECT * FROM user WHERE data ? 'hello';
  </code></pre>
  
  <p>not by jdbc/execute direct write sql like</p>
  
  <pre><code>(jdbc/execute! db-spec ["UPDATE table SET col1 = NOW() WHERE id = ?" 77])
  </code></pre>
  
  <p>I tried to write this query</p>
  
  <pre><code>(jdbc/query conn ["SELECT * FROM user WHERE user_info ? 'hello'"])
  </code></pre>
  
  <p>I got this</p>
  
  <pre><code>org.postgresql.util.PSQLException: 未设定参数值 1 的内容。
  </code></pre>
  
  <p>Then I tried  </p>
  
  <pre><code>(jdbc/query conn ["SELECT * FROM user WHERE user_info ? 'hello'" "?"])
  </code></pre>
  
  <p>I got this</p>
  
  <pre><code>org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  </code></pre>
  
  <p>How do I write a query to filter on a JSON column where user_info has the JSON key <code>hello</code>?</p>