ARTIFACTORY: AQL- How to sort by Properties

ARTIFACTORY: AQL- How to sort by Properties

AuthorFullName__c
Derek Pang
articleNumber
000005487
ft:sourceType
Salesforce
FirstPublishedDate
2022-11-30T11:26:07Z
lastModifiedDate
2022-11-30
VersionNumber
2

There is a limitation with AQL as stated on the wiki page:

Limitation
Sort, limit and offset elements only work in the following cases:

  • Your query does not have an included element
  • If you do have an include element, you only specify fields from the primary domain in it.
For example, in the following query, sort, limit and offset will not work because the primary domain is an item, but the include element specifies that fields from the artifact, module and build domains should be displayed:
items.find().include("artifact","artifact.module","artifact.module.build")

This means that if you search for an item and include the property you will not be able to sort by the property.
items.find({"repo":"example-repo-local"}).include("repo","path", "name", "created", "@build.number")
AQL Architecture reference:

User-added image
While AQL cannot directly sort by the properties you can use an external tool to parse through the returned json and sort it from there.

Here is an example using the jq utility.
$ curl -XPOST -uadmin "<ART_URL>/artifactory/api/search/aql" -H "Content-Type: text/plain" -d 'items.find({"repo":"example-repo-local"}).include("repo","path", "name", "created", "@build.number")' | jq '.results[]' | jq -s -c 'sort_by(.properties[0].value|tonumber) | reverse' | jq

Example response:
[
  {
    "repo": "example-repo-local",
    "path": ".",
    "name": "testtest.txt",
    "created": "2022-10-13T18:32:44.644Z",
    "properties": [
      {
        "key": "build.number",
        "value": "11"
      }
    ]
  },
  {
    "repo": "example-repo-local",
    "path": ".",
    "name": "test2.txt",
    "created": "2022-10-13T18:32:44.644Z",
    "properties": [
      {
        "key": "build.number",
        "value": "3"
      }
    ]
  },
  {
    "repo": "example-repo-local",
    "path": ".",
    "name": "test1.txt",
    "created": "2022-10-13T18:32:44.644Z",
    "properties": [
      {
        "key": "build.number",
        "value": "2"
      }
    ]
  },
  {
    "repo": "example-repo-local",
    "path": ".",
    "name": "test.txt",
    "created": "2022-10-13T18:32:44.644Z",
    "properties": [
      {
        "key": "build.number",
        "value": "1"
      }
    ]
  }
]