Metadata query "Like" syntax?

Highlighted
Occasional Contributor

Metadata query "Like" syntax?

How do I perform a query via metadata using the ILIKE syntax? I can't get it working. Here's what I've tried:

 

Test to see if there are any folders with a "manager" field. Worked great.

 

var query = "managers IS NOT NULL";
var search = client.MetadataManager.ExecuteMetadataQueryAsync(
    query: query,
    from: $"{enterpriseId}.{templateKey}",
    ancestorFolderId: "0"
);
search.Wait();
var numOfEntries = search.Result.Entries.Count();
Debug.Assert(numOfEntries > 1); // Passed 

 

 

Try "ILIKE" syntax as I think it should be written:

 

var query = "managers ILIKE '%flex%'";

 

Throws error:

 

"One or more errors occurred. (The API returned an error [BadRequest | qu5vxwgfddjxlgyw.0eba027c1d3fb47ececd30702f70fd8b4] invalid_query - Failed to parse query: Invalid input ''', expected ' ', '\\r', '\\n', '\\t', '\\f' or valueExpression (line 1, pos 16):\nmanagers ILIKE '%flex%'\n ^\n)"

 

Try without quotes:

 

var query = "managers ILIKE %flex%";

 

Throws error:

 

"One or more errors occurred. (The API returned an error [BadRequest | g33nzagfddlzh2ai.088eee0953c2a54d02ddb76fed2a22a62] invalid_query - Failed to parse query: Invalid input '%', expected ' ', '\\r', '\\n', '\\t', '\\f' or valueExpression (line 1, pos 16):\nmanagers ILIKE %flex%\n ^\n)"

 

Try with double quotes:

 

var query = "managers ILIKE \"%flex%\"";

 

Throws error:

"One or more errors occurred. (The API returned an error [BadRequest | 6ayh24gfddnnqanh.034b2452c21e7b202af1610911848829c] invalid_query - unsupported right-hand side in comparison)"

 

And now I'm just out of ideas.

2 REPLIES 2
Highlighted
Box Employee

Re: Metadata query "Like" syntax?

I think the problem is not that there is a problem with the ILIKE syntax, but that you aren't paramaterizing arguments where expected. In the first example you use "managers IS NOT NULL" which is fine since NULL is a reserved keyword. However in the seond example, you use "managers ILIKE %flex%" - which is not fine, because `query` expects references to fields, operators, keywords, and arguments, but %flex% is a literal. Instead, you should do something like the following, where you specify the argument in`query_params` ... Sorry if the syntax is not exactly write in this language, but hopefully it illustrates the point. 

var query = "managers ILIKE :myArg";
var search = client.MetadataManager.ExecuteMetadataQueryAsync(
    query: query,
	query_params: "myArg":"%flex%",
    from: $"{enterpriseId}.{templateKey}",
    ancestorFolderId: "0"
);
search.Wait();

 

Here's an example of the raw JSON using ILIKE which works just fine - I verified this in Postman.

{
  "from": "{{scope}}.{{templateKey}}",
  "query": "photographer ILIKE :name",
  "query_params": {"name": "dan"},
  "ancestor_folder_id" : "0",
  "order_by": [
    {
      "field_key": "photographer",
      "direction": "asc"
    }
  ],
  "limit": 10
}

 

Box Employee

Re: Metadata query "Like" syntax?

Thank you for clarifying @dandennhardt.

 

@YousefShanawany I've updated the code sample for you on the docs.

 

https://developer.box.com/guides/metadata/queries/syntax/#Pattern-matching


---
Developer Advocate @ Box