Formatting and filtering OCI CLI output

The default output format of the OCI commandline interface is JSON. Every available piece of information is included in the response. Especially when you use it in Shell scripts or applications, it can be difficult to extract the desired information.

This blog post demonstrate the build-in filtering capabilities and the available output formats.

Formatting

Currently two different output formats are supported by the OCI CLI.

  • JSON (default)
  • Table-formatted

To override the default output format, the parameter –output is used.

Error messages are always returned as JSON response, despite what output format is set.

$> oci os object list -bn non-existing-bucket --output table
ServiceError:
{
    "code": "BucketNotFound",
    "message": "Either the bucket named 'non-existing-bucket' does not exist in the namespace 'xxxxxxxxxxxx' or you are not authorized to access it",
    "opc-request-id": "fra-1:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "status": 404
}

JSON

$> oci os object list -bn "my-bucket-210121-1425" --output json

{
  "data": [
    {
      "archival-state": null,
      "etag": "e0030182-3850-4e52-9ef8-567c794b63b8",
      "md5": "H8scWcchoH1XrSuTgTTehA==",
      "name": "expdp.par",
      "size": 193,
      "storage-tier": "Standard",
      "time-created": "2021-02-18T13:09:02.178000+00:00",
      "time-modified": "2021-03-05T15:03:21.412000+00:00"
    },
    {
      "archival-state": null,
      "etag": "647876cf-dd32-4d07-8a75-6e5fb2b8a0de",
      "md5": "Fd3Oqm702wD8TQqt3odybw==",
      "name": "expdp2.par",
      "size": 145,
      "storage-tier": "Standard",
      "time-created": "2021-02-18T12:14:47.533000+00:00",
      "time-modified": "2021-02-18T12:14:47.620000+00:00"
    }
  ],
  "prefixes": []
}

The JSON response is already formatted in a human-readable format.

Table-formatted

$> oci os object list -bn "my-bucket-210121-1425" --output table

+----------------+--------------------------------------+--------------------------+--------------------+--------+--------------+----------------------------------+----------------------------------+
| archival-state | etag                                 | md5                      | name               | size   | storage-tier | time-created                     | time-modified                    |
+----------------+--------------------------------------+--------------------------+--------------------+--------+--------------+----------------------------------+----------------------------------+
| None           | e0030182-3850-4e52-9ef8-567c794b63b8 | H8scWcchoH1XrSuTgTTehA== | expdp.par          | 193    | Standard     | 2021-02-18T13:09:02.178000+00:00 | 2021-03-05T15:03:21.412000+00:00 |
| None           | 647876cf-dd32-4d07-8a75-6e5fb2b8a0de | Fd3Oqm702wD8TQqt3odybw== | expdp2.par         | 145    | Standard     | 2021-02-18T12:14:47.533000+00:00 | 2021-02-18T12:14:47.620000+00:00 |
+----------------+--------------------------------------+--------------------------+--------------------+--------+--------------+----------------------------------+----------------------------------+

Filtering

The query language JMESPath is supported by the OCI CLI. This allows the filtering of the JSON response to the minimum required set of objects and pieces of information. To provide a filter to an OCI CLI request, add the parameter –query.

A good starting point to get familiar with JMESPath is the tutorials area on its website.

As you can see from the above JSON response, all information is encapsulated in an array with the key data. So every query has to start with this key. The following calls would return all available array elements.

$> oci ... --query "data"
$> oci ... --query "data[?]"

If for example I want the first element only or a range of elements, the position(s) has/have to be provided.

$> oci ... --query "data[0]"
$> oci ... --query "data[0:2]"

Now I want to reduce the returned information to the keys name and size. 

$> oci ... --query "data[0].{Name:name,Size:size}"
{
  "Name": "expdp.par",
  "Size": 193
}

$> oci ... --query "data[0].{Size:size,Name:name}" --output table
+-----------+------+
| Name      | Size |
+-----------+------+
| expdp.par | 193  |
+-----------+------+

The column position within the {..} is not honored. Instead, the columns are ordered in alphabetical order. To change the order, add a number to the column names. Be aware that in this case, the column names have to be escaped with \”xxx\”.

$> oci ... --query "data[0].{\"1 Size\":size,\"2 Name\":name}" --output table
+--------+-----------+
| 1 Size | 2 Name    |
+--------+-----------+
| 193    | expdp.par |
+--------+-----------+

Escaping is also required, when the key includes a hyphen, e.g. \”time-created\”.

It is also possible to filter the elements of a JSON array using expressions. For example, get information about a file with the name expdp2.par. As far as I know, wildcard searches are not possible, but most of the use cases can be replaced by using the contains function.

$> oci ... --query "data[?name == 'expdp2.par'].{\"1 Size\":size,\"2 Name\":name}" --output table
+--------+------------+
| 1 Size | 2 Name     |
+--------+------------+
| 145    | expdp2.par |
+--------+------------+

$> oci ... --query "data[?contains(name,'.par')].{\"1 Size\":size,\"2 Name\":name}" --output table
+--------+------------+
| 1 Size | 2 Name     |
+--------+------------+
| 193    | expdp.par  |
| 145    | expdp2.par |
| 169    | expdp3.par |
+--------+------------+

If you have queries which are used frequently, you can define an alias for it in ~/.oci/oci_cli_rc.

$> vi ~/.oci/oci_cli_rc
...
[OCI_CLI_CANNED_QUERIES]
get_expdp_file=data[?name == 'expdp2.par'].{"1 Size":size,"2 Name":name}

Escaping is not required when the oci_cli_rc file is used. The defined alias can then be used for a query.

$> oci ... --query query://get_expdp_file --output table
+--------+------------+
| 1 Size | 2 Name     |
+--------+------------+
| 145    | expdp2.par |
+--------+------------+

Summary

After investigating some time into JMESPath, filtering the JSON response of the OCI CLI is easy and very useful to get rid of all information that is not required. With the help of the table-formatted output, nice reports can be created.

References

8 comments

  1. yeah for some reason this filtering doesn’t work for me.
    oci marketplace listing get –listing-id 58326065 –query ‘data[0]’
    Query returned empty result, no output to show.

    Reply

    1. Hi Marcus,

      do you get an output, when you remove the –query parameter? Can you share this output?
      data[0] is only working, when the returned result contains an array with the name data.

      Cheers
      Christian

      Reply

  2. Hi Christian,
    very nice article!. I have one question. How can I make multiple conditions in one data array? something like –query “data[?contains(type,’FULL’), ?contains(‘time-ended’,’2022-05-04′)]”

    Reply

  4. Hi Chris,

    how to capture this exact “Query returned empty result, no output to show” error using try catch? something like var = “oci cli” , if var == “Query returned empty result, no output to show”. I also try regular expression just in case it’s partial string with extra whitespace etc., just no luck.

    Thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *