Advanced Search Problems with WIld Cards, Parentheisis, and more than one condition

[Mike Lang]

I am attempting Advanced Search using the Confluence API, and I am experiencing the following problems. 

(NOTE: When publishing this topic, the forum made changes to the HTML links below because it said they were invalid.  I hope they didn't change them too much!!!)

If I surround two or more conditions in the URL with parenthesis, an error is returned

THIS WORKS (no parenthesis):

·         http://wiki.intra.lutron.com/rest/api/content/search?cql=type=attachment&title="EODB"

THIS WORKS (parenthesis around each criteria):

·         http://wiki.intra.lutron.com/rest/api/content/search?cql=(type=attachment)&(title="EODB")

THIS DO NOT WORK (parenthesis around BOTH criteria):

·         http://wiki.intra.lutron.com/rest/api/content/search?cql=(type=attachment&title="EODB")

·         {"statusCode":400,"data":{"authorized":false,"valid":true,"errors":[],"successful":false},"message":"Missing closing parenthesis near field (()"}

THIS DO NOT WORK (parenthesis around each and BOTH criteria):

·         http://wiki.intra.lutron.com/rest/api/content/search?cql=((type=attachment)&(title="EODB"))

·         {"statusCode":400,"data":{"authorized":false,"valid":true,"errors":[],"successful":false},"message":"Could not parse cql : ((type=attachment)"}

If I use more than one criteria, it seems that some criteria are not executed

·         http://wiki.intra.lutron.com/rest/api/content/search?cql=type=attachment&title="EODB"&limit=200

·         This returns 200 records (when it should return 0) because the titles were not filtered to only include the file titled "EODB". 

·         It seems that the type and limit were executed, but title was not

·         Examples of the titles returned include:

·         SeeTouchExample.pdf

·         DEPRECATED_Application_(GUI)_Software_Release_Authorization.xlsx

·         image2016-10-7 16:30:12.png

·         image2017-12-1_17-33-38.png

·         image2017-12-1_17-33-59.png

·         image2017-12-4_9-6-15.png

·         http://wiki.intra.lutron.com/rest/api/content/search?cql=title="EODB"&type=attachment&limit=200

·         This returns no records (which is correct), and all I did was swap the order of TYPE and TITLE

Wildcards do not seem to work:

·         http://wiki.intra.lutron.com/rest/api/content/search?cql=title="EODB*"

·         http://wiki.intra.lutron.com/rest/api/content/search?cql=title~"EODB*"

·         Both of these return an empty set

·         http://wiki.intra.lutron.com/rest/api/content/search?cql=title~"EODB"

·         Returns 3 records, as expected

Please help…

Thanks!

Mike

[Stephen Deutsch · Accepted solution]

Hi Mike,

Your major problem is that "and" in CQL is not the ampersand character (&), it is instead the word "AND".

Therefore the URL should look like this: http://wiki.intra.lutron.com/rest/api/content/search?cql=type=attachment+AND+title="EODB"

In this URL, the + is a replacement for the space character. You could use %20 instead if you'd like.

[Mike Lang]

Thanks, Stephen.  This helped some, but I am still having some problems.

Replacing "&" with "+AND+" solved the problem of "unable to use outer parenthesis" Thanks! 

However, the line that you "corrected" for me was working as-is (meaning, it worked using "&")  How was it able to work in that case?!?!  And more confusing, my example below seems to show that I HAVE to use "&", at least with 'limit'.

THIS STILL FAILS:

http://wiki.intra.lutron.com/rest/api/content/search?cql=(type=attachment)+AND+(title~"EODB*ESR")+AND+(limit=200)

ERROR MESSAGE:

{"statusCode":400,"data":{"authorized":false,"valid":true,"errors":[],"successful":false},"message":"Could not parse cql : (type=attachment) AND (title~\"EODB*ESR\") AND (limit=200)"}

BUT THIS WORKS!!! (Doing it "wrong", using "&" instead of "+AND+"):

http://wiki.intra.lutron.com/rest/api/content/search?cql=(type=attachment)+AND+(title~"EODB*ESR")&(limit=200)

Ideas?

Best,

Mike

[Stephen Deutsch]

Hi Mike,

That's because limit is an additional parameter, not a part of CQL.

Here's the documentation for CQL: https://developer.atlassian.com/confdev/confluence-server-rest-api/advanced-searching-using-cql

Here's the documentation for the REST call: https://docs.atlassian.com/atlassian-confluence/REST/6.5.2/#content (you should click on "Search")

I would recommend installing the REST API Browser in your instance, it makes debugging REST calls so much easier: https://marketplace.atlassian.com/plugins/com.atlassian.labs.rest-api-browser/server/overview

So using &limit=200 is not wrong, it is an additional parameter and should therefore be that way. :)

[Mike Lang]

Brilliant!  Solved.  Thank you!

[Stephen Deutsch]

Glad I could help! If you could click on the "Accept as Solution" button I would appreciate it!

[Mike Lang]

For the life of me I cannot find that button!  Where should I be looking?

[Stephen Deutsch]

Thanks for looking so hard! For anyone else who's curious, it's the checkmark button next to the answer :)