Confluence Cloud API search endpoint (/wiki/rest/api/search) not returning results for a CQL query t

Kunal Jha July 8, 2024

Problem Statement: 

 

We are attempting to use the Confluence Cloud API to fetch data from a specific space, created by a particular user within the last 7 days, and containing a specific GitLab URL. While this search works correctly in the Confluence UI, the API endpoint is not returning any results.

Steps to Reproduce

  1. Construct a CQL query with the following parameters:
    • Space: XYZ
    • Text search: URL of a GitLab merge request
    • Content type: Page
    • Contributor: user-x
    • Last modified: Within the last 7 days
  2. Send a GET request to the Confluence Cloud API search endpoint (/wiki/rest/api/search) with the constructed CQL query.
  3. Observe that the API returns no results.
  4. Perform the same search using the Confluence UI search functionality.
  5. Observe that the UI search returns the expected results.

Expected Behavior

The API search should return the same results as the UI search when using equivalent search criteria.

Actual Behavior

The API search returns no results, while the UI search successfully finds the expected pages.

 


 

https://developer.atlassian.com/cloud/confluence/rest/v1/api-group-search/#api-wiki-rest-api-search-get

 

Below is the CQL query used. 

 

```

cql = ('space = XYZ AND ' 'text ~ "https://gitlab.company.internal/company/group/repo-old/-/merge_requests/123" AND ' 'type = page AND ' 'contributor in (user-x) AND ' 'lastModified >= now("-7d")')

```

Equivalent UI Search Works fine

 

https://company.url/wiki/search?text=space%3D%22QDN%22%20AND%20type%3D%22page%22%20AND%20text%20~%20%22takamol%20group%20repo-old%22%20AND%20contributor%3D%22user-x2%22%20AND%20lastmodified%20%3E%3D%20past30Days&spaces=XYZ&lastModified=past7Days&type=page

 

Needs Investigation ?

 

  1. Are there any differences in how the API and UI interpret CQL queries?
  2. Could there be permission issues that affect API searches differently from UI searches?
  3. Are there any known limitations or bugs with the API search functionality?
  4. How can we debug the API request to understand why it's not returning results?

Impact

Unable to programmatically search and retrieve Confluence content that is known to exist and is accessible through the UI. This impacts our ability to automate certain processes and integrate Confluence data with other systems.

3 answers

1 accepted

0 votes
Answer accepted
Kunal Jha July 22, 2024

I was able fix this with below approach. 

So My Merge Request URL was below  :

`https://gitlab.company.internal/company/group/repo-old/-/merge_requests/123`

MR Link

https://gitlab.company.internal/company/group/repo-old/-/merge_requests/123

 

CQL : 

cql = f'space=QDN and text ~ "https:\/\/gitlab\.company\.internal\/company\/group\/repo\-old\/\-\/merge_requests\/123" and type = page'

 

Script To Lookup For URL Link on Confluence Using CQL

import requests
from requests.auth import HTTPBasicAuth
import json
import urllib.parse

url = "https://mycompany.atlassian.net/wiki/rest/api/search"

auth = HTTPBasicAuth(username="user@password", password="Pass***=****")

headers = {
"Accept": "application/json"
}

cql = f'space=XYZ and text ~ "https:\/\/gitlab\.company\.internal\/company\/group\/repo\-old\/\-\/merge_requests\/123" and type = page'



query = {
'cql': cql
}


response = requests.request(
"GET",
url,
headers=headers,
params=query,
auth=auth
)

print(response.text)
json_data = json.loads(response.text)["results"][0]["title"]
print(json.dumps(json_data, indent=4))
0 votes
Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 9, 2024

My remarks:

  1. Note that the CQL will be executed on behalf of the user who owns the API token! I mean that if you are running the search through the UI using user account Alice, but then run them through the API using token owner Bob, then they will return different results!
  2. Also, what is the HTTP response's status code returned by the API call? 
Kunal Jha July 9, 2024

1.  Using the same user for both UI and API operation.

2. Response of the API Request

{
"results": [],
"start": 0,
"limit": 25,
"size": 0,
"_links": {
"base": "https://your-domain.atlassian.net/wiki",
"context": "/wiki",
"self": "https://your-domain.atlassian.net/wiki/rest/api/content/search?cql=space%20=%20XYZ%20AND%20text%20~%20%22https://gitlab.company.internal/company/group/repo-old/-/merge_requests/123%22%20AND%20lastmodified%20%3E=%20now(%22-7d%22)"
}
}
Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 9, 2024

Can you try this with a trivial query string like just "space=XYZ"?

If it worked, then maybe the whole problem is with not properly escaping the tilde or quotation or space characters... :-?

Kunal Jha July 9, 2024

Below queries works

cql = ('space = XYZ AND '
'type = page AND '
'contributor in (user-x) AND '
'lastModified >= now("-7d")')

 


cql = ('space = XYZ AND '
'text ~ "repo-old" AND '
'type = page AND '
'contributor in (user-x) AND '
'lastModified >= now("-7d")')

 

But when i add the URL it fails completely

cql = ('space = XYZ AND '
'text ~ "https://gitlab.company.internal/company/group/repo-old/-/merge_requests/123" AND '
'type = page AND '
'contributor in (user-x) AND '
'lastModified >= now("-7d")')
0 votes
Kunal Jha July 8, 2024

Script Compiled from Sample to Search the Gitlab Link in the Confluence Space Document.

import requests
from requests.auth import HTTPBasicAuth
import json

url = "https://your-domain.atlassian.net/wiki/rest/api/search"
auth = HTTPBasicAuth("email@example.com", "<api_token>")
headers = {
"Accept": "application/json"
}

cql = ('space = XYZ AND '
'text ~ "https://gitlab.company.internal/company/group/repo-old/-/merge_requests/123" AND '
'type = page AND '
'contributor in (user-x) AND '
'lastModified >= now("-7d")')

query = {
'cql': cql
}

response = requests.request(
"GET",
url,
headers=headers,
params=query,
auth=auth
)

print(json.dumps(json.loads(response.text), sort_keys=True, indent=4, separators=(",", ": ")))

 This is just input not the answer

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS
AUG Leaders

Atlassian Community Events