Jira Rest API - issue field orderable key value problems

The rest api returns the following json for a field.

{
  "id": "customfield_10101",
  "key": "customfield_10101",
  "name": "New custom field",
  "untranslatedName": "New custom field",
  "custom": true,
  "orderable": true,
  "navigable": true,
  "searchable": true,
  "clauseNames": [
    "cf[10101]",
    "New custom field"
  ],
  "schema": {
    "type": "project",
    "custom": "com.atlassian.jira.plugin.system.customfieldtypes:project",
    "customId": 10101
  }
}

I am trying to generate a list of fields which a user can order their JQL by. Naturally you’d think that this would be simple using the “orderable” value but no.

For example the field id “updated” has this orderable value set to false. However… in Jira itself you are able to do an advanced search and select updated as the order by field.

Next comes field id “customfield_10026” (Approvals). This has orderable set to true but if you try to use it Jira returns the following… Not able to sort using field ‘customfield_10026’.

This is not the case for every field returned from the api. But based on this how is it possible to determine and generate a definitive list of fields which a user can order by? This is evidently possible in Jira’s advanced search as it allows you to pick from a list or search for the field.

Any help would be greatly appreciated.

Hi @RichardWattie,

You can query field metadata relevant to JQL searches using /rest/api/3/jql/autocompletedata.

The response will include the “orderability” of each field with respect to JQL search results.

Regards,
Dugald

Hi @dmorrow,

I’m not sure how this has any benefits. Looking at the same two fields I referred to in my original post these are the results of a query to /rest/api/3/jql/autocompletedata

{
  "value": "Approvals",
  "displayName": "Approvals - cf[10026]",
  "orderable": "true",
  "searchable": "true",
  "cfid": "cf[10026]",
  "operators": [
    "="
  ],
  "types": [
    "com.atlassian.servicedesk.plugins.approvals.internal.searcher.ApprovalsDataTypes$ApprovalDataType"
  ]
},
{
  "value": "updated",
  "displayName": "updated",
  "orderable": "true",
  "searchable": "true",
  "operators": [
    "=",
    "!=",
    "in",
    "not in",
    "is",
    "is not",
    "<",
    "<=",
    ">",
    ">="
  ],
  "types": [
    "java.util.Date"
  ]
}

As you can see they both show orderable as true yet Jira will not allow you to order by Approvals.
Am I misunderstanding this?

Hi @RichardWattie,

Sorry, my previous response was a little terse and misleading. When you look at the response from /rest/api/3/jql/autocompletedata, there is a field called operators. I think this field indicates the “sortability” of a field. If it includes “<”, “<=”, “>” and “>=” then it is sortable.

Let me know if this makes sense and matches what you see.

Regards,
Dugald

Hi @dmorrow,

I’m still not sure this is an accurate way to determine a definitive list. For example in Jira it is possible to order by summary but this response does not comply with…

If it includes “<”, “<=”, “>” and “>=” then it is sortable.

{
  "value": "summary",
  "displayName": "summary",
  "orderable": "true",
  "searchable": "true",
  "operators": [
    "~",
    "!~",
    "is",
    "is not"
  ],
  "types": [
    "java.lang.String"
  ]
}

What is the purpose of the orderable property?

Hi @RichardWattie,

Yes, that makes sense - we’d need to also consider the type of the field.

There seems to be a gap in our documentation, so I’ve created https://ecosystem.atlassian.net/browse/ACJIRA-2268 to address this.

Regards,
Dugald

Hi @dmorrow,

So is there only certain field types that can be ordered by too?
Is there a list of types which maybe I could use in combination with the operators to filter out a list?

I’m trying to create a drop down list for a user to select an order by field and just think it’s a bad user experience for me to provide them an option which ends up returning an error messsage. It’d be nice to be able to provide an accurate list

Hi @RichardWattie,

I’m not aware of any good documentation for this. Hopefully ACJIRA-2268 will provide that.

Regards,
Dugald

Just to make this even more fun…

The /rest/api/2/field end-point does not agree with the /rest/api/2/jql/autocompletedata end-point. For example, the creator field is returned by /rest/api/2/field as:

{
	"id": "creator",
	"key": "creator",
	"name": "Creator",
	"custom": false,
	"orderable": false,
	"navigable": true,
	"searchable": true,
	"clauseNames": ["creator"],
	"schema": {
		"type": "user",
		"system": "creator"
	}
}

That same creator field is returned by /rest/api/2/jql/autocompletedata as:

{
      "value": "creator",
      "displayName": "creator",
      "auto": "true",
      "orderable": "true",
      "searchable": "true",
      "operators": [
        "=",
        "!=",
        "in",
        "not in",
        "is",
        "is not"
      ],
      "types": [
        "com.atlassian.jira.user.ApplicationUser"
      ]
}

Note the difference in the values of the “orderable” attribute in the previous results. In this case, the autocompletedata result is the correct one: you can include ‘creator’ in your ORDER BY clause.