How to do sorting with jmespath both ASC and DESC

Question

I need help with expression for sorting both ascending and descending for the same data.

I can have multiple expression separated by pipe but i can't seem to find any example that does both ASC and DESC in the same call.

So what i am looking for is equal to following sql query.

select * 
from dummyData 
order by id, code desc, update_only

Here is sample.

[
  {
    "id": 1,
    "code": "y",
    "update_only": 0.0
  },
  {
    "id": 2,
    "code": "a",
    "update_only": 0.0
  },
  {
    "id": 1,
    "code": "z",
    "update_only": 0.0
  },
  {
    "id": 2,
    "code": "b",
    "update_only": 0.0
  }
]

I can do following for the ordering

sort_by(array elements, expression->number|expression->string expr)

How can i do the following with one call rather than the two calls that i have?

sort_by(myarray, &id | &update_only)[];
reverse(sort_by(myarray, &code))[];

Or by doing multiple calls, don't want to do this as well.

result1 = sort_by(myarray, &id)[];
result2 = reverse(sort_by(result1, &code))[];
resilt3 = sort_by(myarray, &update_only)[];

user_2123530 · Answer

Your actual expression is incorrect, you will realise, if you change a value of update_only that &id | &update_only is not a thing.

What is described in the documentation:

expression->number|expression->string expr

Is actually meaning that the expression can either be of type string or of type number, not that you can chain multiple sorting using the pipe sign.

An explanation of this is made here:

JMESPath has various built-in functions that operate on different data types, documented below. Each function below has a signature that defines the expected types of the input and the type of the returned output:
return_type function_name(type $argname)
return_type function_name2(type1|type2 $argname)
The list of data types supported by a function are:

number (integers and double-precision floating-point format in JSON)

string

boolean (true or false)

array (an ordered, sequence of values)

object (an unordered collection of key value pairs)

null

expression (denoted by &expression)

With the exception of the last item, all of the above types correspond to the types provided by JSON.

If a function can accept multiple types for an input value, then the multiple types are separated with |. If the resolved arguments do not match the types specified in the signature, an invalid-type error occurs.

^{Source: https://jmespath.org/specification.html#built-in-functions, emphasis, mine}

Now, what want to achieve is pretty simple.
When you are sorting on multiple columns, it is like chaining the sorts, from the lower priority to the upper priority.

So that means that you can do (in pseudo code):

sort by id (
  reverse (
    sort by code (
      reverse (  
      // ^--- this is the trick, so when you will reverse it
      // again, to have the DESC sorting on `code`, you'll end
      // up with the sorting of `update_only` in the "correct" order
        sort by update_only
      )
    )
  )
)

So, translated in a JEMSPath expression:

sort_by(reverse(sort_by(reverse(sort_by(@,&update_only)),&code)),&id)

This expression on the following JSON (added an extra case from your example to showcase the update_only sorting:

[
  {
    "id": 1,
    "code": "y",
    "update_only": 0.0
  },
  {
    "id": 2,
    "code": "a",
    "update_only": 0.0
  },
  {
    "id": 1,
    "code": "z",
    "update_only": 0.0
  },
  {
    "id": 2,
    "code": "b",
    "update_only": 1.0
  },
  {
    "id": 2,
    "code": "b",
    "update_only": 0.0
  }
]

Will give:


[
  {
    "id": 1,
    "code": "z",
    "update_only": 0
  },
  {
    "id": 1,
    "code": "y",
    "update_only": 0
  },
  {
    "id": 2,
    "code": "b",
    "update_only": 0
  },
  {
    "id": 2,
    "code": "b",
    "update_only": 1
  },
  {
    "id": 2,
    "code": "a",
    "update_only": 0
  }
]

How to do sorting with jmespath both ASC and DESC

1 Answers