"WHERE clause" involving ARRAY comparison of values - Postgresql

[SrinivasanViswanathan]

Hi,

Am using Postgresql database. I need to generate SQL Where clause which involves comparison of Array of values with an Array column in the database and below will be the syntax for the same in Postgresql -

ARRAY['A','B']::text[] && testColumn

In above syntax, am comparing if values A, B are available in the "text[]" array column with name - "testColumn".

In the js query builder component, there is no operator to handle this scenario to create the SQL where clause from the component. So, i have created an operator - "in_string_array" to handle this, please refer js fiddle - https://jsfiddle.net/rpqvgLj0/14/

Am able to generate the SQL where clause successfully from the querybuilder component.

However, am not able to do the reverse, that is, i have a SQL Where clause with the syntax - ARRAY['A','B']::text[] && testColumn Am not able to paint the query builder component on browser for the above SQL Where clause syntax using setRulesFromSQL function. Please refer jsfiddle - https://jsfiddle.net/rpqvgLj0/14/

Am getting error in console as below - Uncaught Error: NOTHING CONSUMED: Stopped at - '['A','B']::text[] && testArray' at new Lexer (sql-parser.js:32) at Object.exports.tokenize (sql-parser.js:276) at Object.exports.parse (sql-parser.js:1327) at QueryBuilder.getRulesFromSQL (query-builder.standalone.js:5871) at QueryBuilder.setRulesFromSQL (query-builder.standalone.js:6041) at jQuery.fn.init.$.fn.queryBuilder (query-builder.standalone.js:4046) at (index):79 at dispatch (jquery-2.1.3.js:4430) at elemData.handle (jquery-2.1.3.js:4116)

I tried making modifications in SQL parser js by including the words -"ARRAY", "[", "]" etc. as part of SQL parser js keywords and symbols. However, they were not working and i was getting error in console - Uncaught Error: Parse error on line 2: Unexpected 'ARRAY['

I have been trying this for more than 2 days now. Any pointers would be of great help. An understanding how the parsing works here would also help so that i can extend and customize the SqlParser to make it work.

I had created this issue in the jquery-querybuilder repo. However, since my issue is in the SQLParser side, am creating the issue here. Please help.

Thanks and regards, V. Srinivasan

[mihailacusteanu]

@SrinivasanViswanathan @mistic100 have you found a fix for this? I am trying to create a custom operator ~ and the I implemented the ruleToSQL to save the sql in the database but while trying to extract the rules I get this: Uncaught Error: NOTHING CONSUMED: Stopped at - '~ '[A-Fa-f0-9]{8}'', the sql is my_field ~ '[A-Fa-f0-9]{8}'

Any idea?