OCI CLI advanced feature "--query" example with filter, sorting and limits

 (py37) C:\Oracle\Scripts\OCI>oci db version list -c ocid1.tenancy.oc1..aaaaaaaas3auzx5dv5yjmtszag37ri4lpmvrnzszw5dddmngzg6goyajy2ta --db-system-shape "VM.Standard2.1" --all --profile donghua --output table

+-----------------------------+-----------------------+----------------------+--------------+-----------------+
| is-latest-for-major-version | is-preview-db-version | is-upgrade-supported | supports-pdb | version         |
+-----------------------------+-----------------------+----------------------+--------------+-----------------+
| True                        | False                 | False                | False        | 11.2.0.4        |
| False                       | False                 | False                | False        | 11.2.0.4.200114 |
| False                       | False                 | False                | False        | 11.2.0.4.200414 |
| False                       | False                 | False                | False        | 11.2.0.4.200714 |
| False                       | False                 | False                | False        | 11.2.0.4.201020 |
| True                        | False                 | False                | True         | 12.1.0.2        |
| False                       | False                 | False                | True         | 12.1.0.2.200114 |
| False                       | False                 | False                | True         | 12.1.0.2.200414 |
| False                       | False                 | False                | True         | 12.1.0.2.200714 |
| False                       | False                 | False                | True         | 12.1.0.2.201020 |
| True                        | False                 | False                | True         | 12.2.0.1        |
| False                       | False                 | False                | True         | 12.2.0.1.200114 |
| False                       | False                 | False                | True         | 12.2.0.1.200414 |
| False                       | False                 | False                | True         | 12.2.0.1.200714 |
| False                       | False                 | False                | True         | 12.2.0.1.201020 |
| True                        | False                 | False                | True         | 18.0.0.0        |
| False                       | False                 | False                | True         | 18.10.0.0       |
| False                       | False                 | False                | True         | 18.11.0.0       |
| False                       | False                 | False                | True         | 18.12.0.0       |
| False                       | False                 | False                | True         | 18.9.0.0        |
| True                        | False                 | True                 | True         | 19.0.0.0        |
| False                       | False                 | False                | True         | 19.6.0.0        |
| False                       | False                 | True                 | True         | 19.7.0.0        |
| False                       | False                 | True                 | True         | 19.8.0.0        |
| False                       | False                 | True                 | True         | 19.9.0.0        |
| True                        | False                 | False                | True         | 21.0.0.0        |
| False                       | False                 | False                | True         | 21.1.0.0        |
+-----------------------------+-----------------------+----------------------+--------------+-----------------+

# https://jmespath.org/
# filter by "is-latest-for-major-version", which is boolean
# sort by is-upgrade-supported, which is also boolean, sort requires number or to_string
# [0:10] to limit first 10 rows

oci db version list --query "reverse(sort_by(data[? \"is-latest-for-major-version\" ],&to_string(\"is-upgrade-supported\"))|[0:10].{\"is-upgrade-supported\":\"is-upgrade-supported\",\"version\":\"version\"})" -c ocid1.tenancy.oc1..aaaaaaaas3auzx5dv5yjmtszag37ri4lpmvrnzszw5dddmngzg6goyajy2ta --db-system-shape "VM.Standard2.1" --all --profile donghua --output table

+----------------------+----------+
| is-upgrade-supported | version  |
+----------------------+----------+
| True                 | 19.0.0.0 |
| False                | 21.0.0.0 |
| False                | 18.0.0.0 |
| False                | 12.2.0.1 |
| False                | 12.1.0.2 |
| False                | 11.2.0.4 |
+----------------------+----------+

oci db version list --query "data[*].{\"version\":\"version\"}" -c ocid1.tenancy.oc1..aaaaaaaas3auzx5dv5yjmtszag37ri4lpmvrnzszw5dddmngzg6goyajy2ta --db-system-shape "VM.Standard2.1" --all --profile donghua --output table

oci db version list --query "reverse(data[?contains(\"version\",'19.')].{\"version\":\"version\"})" -c ocid1.tenancy.oc1..aaaaaaaas3auzx5dv5yjmtszag37ri4lpmvrnzszw5dddmngzg6goyajy2ta --db-system-shape "VM.Standard2.1" --all --profile donghua --output table