Use the Community API to query tags

Screen Shot 2017-08-01 at 4.50.03 PM.png

Let's talk about Tags

There are over 16k tags on the Community. You can view the most popular tags here.

To query posts by tags we can use the public Community API v2. The examples below only work for public posts, not posts in private boards. These are anonymous requests and can access data available to users who are not signed into the Community.

Using the API

Here is the foundation of our API request:

https://community.atlassian.com/api/2.0/search?q=

Next, we're going to use a special query language to find our content.

LiQL, a SQL-like query language. Use a LiQL statement to define search criteria for any complex query.

Okay, then.. let's make a LiQL query!

https://community.atlassian.com/api/2.0/search?q= SELECT * FROM messages

> View this response

This gets us a pile of JSON that is a list of unsorted messages and it is capped at 25 (more on that later).

More complex queries

Let's add some more parameters here to search by tags

WHERE tags.text = 'jira service desk'

WHERE tags.text IN ('jira service desk', 'jsd', 'jira-service-desk')

Let's limit these messages to Questions:

AND conversation.style='qanda'

Maybe we want only topic messages that have no replies

AND depth = 0 AND replies.count(*) = 0

Here we go:

https://community.atlassian.com/api/2.0/search?q=SELECT * FROM messages WHERE tags.text IN ('sourcetree') AND conversation.style='qanda' AND replies.count(*) = 0 AND depth = 0

> View this response

Can I sort by date? Sounds useful to me.

ORDER BY post_time DESC

Getting closer.. why are there are 25 messages? That's the default query limit. Let's increase it.

LIMIT 1000

Looking good. Let's put that all together...

https://community.atlassian.com/api/2.0/search?q=SELECT * FROM messages WHERE tags.text IN ('sourcetree') AND conversation.style='qanda' AND replies.count(*) = 0 AND depth = 0 ORDER BY post_time DESC LIMIT 1000

> View this response

It looks a bit ugly. Your computer shouldn't care! Okay, you're right. You can use a browser extension or another tool.. but we can make it look better in the browser without all that. Add this to the URL.

&api.pretty_print=true

Nice work!

Bringing it all together

That query we just created is the same one that powers this page:

https://community.atlassian.com/t5/tag/sourcetree/tg-p?style=qanda&sort=recent&unanswered=true

But, hopefully you will find the JSON data useful. Remember, unlike the link above, you can combine multiple tags into a single query. That's all for now folks.

Add your comments below with specific questions or concerns.

 

>> Updates based on comments

Query large sets of messages

To query over large sets of results you add the following to the very end of your query:

OFFSET 1000

The OFFSET value must be divisible by the LIMIT value

Limit the query response

Select only the parts of the message you need instead of the wildcard *

SELECT view_href, subject, body FROM ...

 

Keep the questions coming!

 

16 comments

MattS
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 1, 2017

Handy stuff, thanks.

Is there a way to page a large set of results. Guessing at a limit of 1000 seems a bit arbitrary. And can we get a link to the LiQL docs please.

Monique vdB
Community Manager
Community Managers are Atlassian Team members who specifically run and moderate Atlassian communities. Feel free to say hello!
August 1, 2017

@Steven F Behnke I wanted to make sure you saw this also!

Tyler T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 1, 2017

@MattS, great questions. For large sets of results you could use this:

OFFSET 1000

 That would get the 2nd page of results.

The docs are here and you'll need to register for an account: https://community.lithium.com/t5/Developer-Documentation/bd-p/dev-doc-portal?section=commv2

In the meantime, if you have specific questions I can try and answer them in another post or in this reply thread.

MattS
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 1, 2017

Thanks!

Like Tyler T likes this
Steven F Behnke
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 1, 2017

:o

My mind is blown. I have no idea what to do with this yet... But thanks! My mind is a spinning!

Like Tyler T likes this
Nick Muldoon
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 20, 2017

Thanks Tyler, we've been using this for a little while now, pulling related posts into our JIRA instance so we can work like regular support requests.

 

Greatly appreciated.

Like # people like this
Monique vdB
Community Manager
Community Managers are Atlassian Team members who specifically run and moderate Atlassian communities. Feel free to say hello!
August 21, 2017

@Daniel Wester is doing some amazing stuff with this too -- hopefully we can talk him into writing an article about it. :) 

Daniel Wester
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 2, 2017

Wanted to drop this out there in case there are people that aren't on the Atlassian Developer Community. https://community.developer.atlassian.com/t/atlassian-community-integration-with-jsd/8153

Like Tyler T likes this
Fadoua
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 6, 2018

Thanks @Monique vdB for sharing the link! Very useful.

Jimmy Seddon
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 1, 2021

I know this was written a number of years ago, but @Monique vdB just pointed it out to me and this was super helpful in a project I'm working on.  Thank you @Tyler T!

Like # people like this
Chak Leung January 20, 2022

Late comment but thanks for this! Very useful :) 

I have a question about the queries that some of these return. It says if I want to get the tags for this post it suggests running:

SELECT * FROM tags where messages.id = '618155'
Which works. But this query fails because of a syntax error (604 error):
SELECT * FROM tags where messages.id in ('618155')
I want to use this so I can get the tags for multiple posts but that doesn't seem possible. I find this a bit odd since I can query the messages table with this syntax like this:
SELECT * FROM messages where parent.id in ('618155')

I could iteratively query every post but feel like it makes sense to do it all in one go. Any workarounds for this? Thanks!

Like # people like this
Tyler T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 20, 2022

@Chak Leung I'm no longer working on the Community but would recommend browsing the Khoros Community and posting your question there: https://community.khoros.com/t5/Community-Discussions/ct-p/communitydiscussions

Khoros powers this community website and their community has a lot of developer discussion. You might be able to access the API docs as well (I don't have that link handy unfortunately).

Like # people like this
Chak Leung January 21, 2022

Thanks @Tyler T, their docs is exactly what I needed! 

Like # people like this
Tyler T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 21, 2022

@Chak Leung awesome! Could you post your solution and a link to the docs here in case other folks come across this post?

Like Monique vdB likes this
Dhruv May 18, 2022

Hi,

I'm facing an issue with a query where i'm trying to find data from lithium where subject doesn't matches a substring.
I want to acheive something like this

https://community.atlassian.com/api/2.0/search?q= SELECT * FROM messages WHERE SUBJECT NOT MATCHES 'sync'

But NOT operator doesn't works for me and i get error : 604 

Tyler T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 23, 2022

Hi @Dhruv , I don't think the NOT operator is the issue because removing it still results in the same error.

I'd recommend checking out the Khoros forum to resolve your issue: https://community.khoros.com/t5/Community-Discussions/ct-p/communitydiscussions

Comment

Log in or Sign up to comment
TAGS
AUG Leaders

Atlassian Community Events