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.

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

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

dandennhardt

June 02, 2020 07:45

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.

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

cbetta

June 04, 2020 02:11

Thank you for clarifying .

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

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

Comments