• NULL / NOT NULL
• IDENT - comparing two columns
• LIKE and ILIKE
• (not) IN
• Regex
• Similarity
• tsquery
• JSONB contains (@>)

NULL / NOT NULL

To search for (not) NULL entries, use the null value in json:

#/data/extensions/widget/query

{  "conds" : {"me.query" : null }}

#/data/extensions/widget/query

"conds" : {
  "me.query" : {
      "-not" : null
    }
  }

IDENT - comparing two columns

#/data/system/entity/query

{ "conds" : {"me.name" : {"-ident": "module.name"}} }

WHERE ( me.name = module.name )

http://search.cpan.org/~ilmari/SQL-Abstract-1.84/lib/SQL/Abstract.pm#-ident

LIKE and ILIKE

#/data/system/entity/query
{ "conds" : {"me.name" : {"-like": "%a%"}} }
{ "conds" : {"me.name" : {"-not_like": "%a%"}} }
{ "conds" : {"me.name" : {"-ilike": "%a%"}} }
{ "conds" : {"me.name" : {"-not_ilike": "%a%"}} }

WHERE ( me.name like ? )
WHERE ( me.name not like ? )

(not) IN

#/data/system/entity/query
{ "conds" : {"me.name" : {"-in": [1,2]}} }
{ "conds" : {"me.name" : {"-not_in": [3,4]}} }

WHERE ( me.name in (?) )
WHERE ( me.name not in (?) )

Regex

The regex operators (~, !~, ~*, !~*) are supported:

#/data/system/entity/query

"conds" : {"me.name" : {"~*": "module.name"}}

WHERE ( me.name ~* ? )

If a column on a big table is not indexed correctly, this search option will not be allowed.

Similarity

This is an extension for IronAPI.

{"attrs" : {"order_by" : {"-similarity" : ["me.name","constant"]}, "columns" : "me.name"}}

This will be translated to sql ORDER BY me.name <-> ? : "constant".

If a column on a big table is not indexed correctly, this search option will not be allowed.

tsquery

This is an extension for IronAPI. The attrs are just for reference, they are not needed for the search query!

#/data/system/entity/query
{
  "conds" : {"-tsquery" : ["english","me.name","data"] },
  "attrs" : {"+select" : "to_tsquery('english',me.name)", "+as" : "tsquery_name"}
}

will be translated to

  WHERE ( to_tsvector(?,me.name) @@ to_tsquery(?, ? ) ): 'english', 'english', 'data'

If a column on a big table is not indexed correctly, this search option will not be allowed.

JSONB contains (@>)

Does the left JSONB value contain the right JSONB path/value entries at the top level?

#/data/core/customer/query
{
  "conds" : {"me.phonenumbers" : {"@>" : {"mobile": "+4354359"} },
}