{"action":"created","issue":{"url":"https://api.github.com/repos/tgriesser/knex/issues/519","labels_url":"https://api.github.com/repos/tgriesser/knex/issues/519/labels{/name}","comments_url":"https://api.github.com/repos/tgriesser/knex/issues/519/comments","events_url":"https://api.github.com/repos/tgriesser/knex/issues/519/events","html_url":"https://github.com/tgriesser/knex/issues/519","id":44816649,"number":519,"title":"Conflict with Postgresql jsonb operators","user":{"login":"metainfa","id":7137988,"avatar_url":"https://avatars.githubusercontent.com/u/7137988?v=3","gravatar_id":"","url":"https://api.github.com/users/metainfa","html_url":"https://github.com/metainfa","followers_url":"https://api.github.com/users/metainfa/followers","following_url":"https://api.github.com/users/metainfa/following{/other_user}","gists_url":"https://api.github.com/users/metainfa/gists{/gist_id}","starred_url":"https://api.github.com/users/metainfa/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/metainfa/subscriptions","organizations_url":"https://api.github.com/users/metainfa/orgs","repos_url":"https://api.github.com/users/metainfa/repos","events_url":"https://api.github.com/users/metainfa/events{/privacy}","received_events_url":"https://api.github.com/users/metainfa/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/tgriesser/knex/labels/bug","name":"bug","color":"fc2929"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":12,"created_at":"2014-10-03T15:47:50Z","updated_at":"2015-07-13T06:38:31Z","closed_at":null,"body":"Hi!\r\n\r\nI have sql:\r\n\r\n```sql\r\nSELECT id, name, price, files->'photos'->0->>'name' as photo, annotation\r\nFROM product\r\nWHERE tags ?& array['17']\r\nORDER BY (tags->>'17')::int\r\nLIMIT 10\r\n\r\n```\r\n\r\n## If i run this sql direct in postgresql client it work fine\r\n## If i run this sql through node-postgres it work fine again:\r\n\r\n```javascript\r\nvar pg = require('pg');\r\n\r\nvar connPg = \"postgres://user:password@localhost/mydbname\";\r\nvar clientPg = new pg.Client(connPg);\r\nvar sqlProductsByTags = [\r\n  \"SELECT id, name, price, files->'photos'->0->>'name' as photo, annotation \",\r\n  \"FROM product \",\r\n  \"WHERE tags ?& array['17'] \",\r\n  \"ORDER BY (tags->>'17')::int \",\r\n  \"LIMIT 10 \"\r\n];\r\n\r\nclientPg.connect(function(err) {\r\n  if(err) {\r\n    return console.error('could not connect to postgres', err);\r\n  }\r\n  //console.log('SQL = ' + sqlProductsByTags.join(''));\r\n\r\n  clientPg.query(sqlProductsByTags.join(''), function(err, result) {\r\n    if(err) {\r\n      return console.error('error select products by tags', err);\r\n    }\r\n\r\n    for(var i = 0, max = result.rows.length; i < max; i++) {\r\n      console.log('name: ' +  result.rows[i].name);\r\n    }\r\n\r\n    clientPg.end();\r\n  });\r\n});\r\n```\r\n## If i run raw sql by knex, it fail with error (below listed): \r\n\r\n```javascript\r\nvar connPg = \"postgres://user:password@localhost/mydbname\"; \r\nvar pg = require('knex')({\r\n  client: 'pg',\r\n  connection: connPg\r\n});\r\nvar sqlProductsByTags = [\r\n  \"SELECT id, name, price, files->'photos'->0->>'name' as photo, annotation \",\r\n  \"FROM product \",\r\n  \"WHERE tags ?& array['17'] \",\r\n  \"ORDER BY (tags->>'17')::int \",\r\n  \"LIMIT 10 \"\r\n];\r\n\r\npg.raw(sqlProductsByTags.join(''))\r\n```\r\n\r\nThis Error\r\n```sh\r\n{ [error: syntax error (at or near \"$1\")]\r\n  name: 'error',\r\n  length: 142,\r\n  severity: 'ERROR',\r\n  code: '42601',\r\n  detail: undefined,\r\n  hint: undefined,\r\n  position: '98',\r\n  internalPosition: undefined,\r\n  internalQuery: undefined,\r\n  where: undefined,\r\n  file: 'scan.l',\r\n  line: '1053',\r\n  routine: 'scanner_yyerror' }\r\n```\r\n\r\nI think that it fail because of i used jsonb operator '?&', precise only one symbol '?'\r\n[Reference to PostgreSQL JSONB operators](http://www.postgresql.org/docs/9.4/static/functions-json.html)\r\n"},"comment":{"url":"https://api.github.com/repos/tgriesser/knex/issues/comments/120829505","html_url":"https://github.com/tgriesser/knex/issues/519#issuecomment-120829505","issue_url":"https://api.github.com/repos/tgriesser/knex/issues/519","id":120829505,"user":{"login":"DenisGorbachev","id":829578,"avatar_url":"https://avatars.githubusercontent.com/u/829578?v=3","gravatar_id":"","url":"https://api.github.com/users/DenisGorbachev","html_url":"https://github.com/DenisGorbachev","followers_url":"https://api.github.com/users/DenisGorbachev/followers","following_url":"https://api.github.com/users/DenisGorbachev/following{/other_user}","gists_url":"https://api.github.com/users/DenisGorbachev/gists{/gist_id}","starred_url":"https://api.github.com/users/DenisGorbachev/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DenisGorbachev/subscriptions","organizations_url":"https://api.github.com/users/DenisGorbachev/orgs","repos_url":"https://api.github.com/users/DenisGorbachev/repos","events_url":"https://api.github.com/users/DenisGorbachev/events{/privacy}","received_events_url":"https://api.github.com/users/DenisGorbachev/received_events","type":"User","site_admin":false},"created_at":"2015-07-13T06:38:30Z","updated_at":"2015-07-13T06:38:30Z","body":"@tgriesser +1, I use JSONB all over the place :)"}}