Json queries with JMEPath and jq

What is JSON

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. JSON is built on two structures:

• A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array.
• An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence.

An object

is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma).

An array

is an ordered collection of values. An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by , (comma).

A value

can be a string in double quotes, or a number, or true or false or null, or an object or an array. These structures can be nested.

A string

is a sequence of zero or more Unicode characters, wrapped in double quotes, using backslash escapes. A character is represented as a single character string. A string is very much like a C or Java string.

A number

is very much like a C or Java number, except that the octal and hexadecimal formats are not used.

Interactive tools

jqplay online

Online jq playground.

Jiq

Install Jiq

1. Install jq
2. Install Golang <1.7
3. Export environment variables eg. to .bashrc or .profile files.

   export GOPATH=/home/ubuntu/.local/go
   export PATH="$PATH:$HOME/.local/go"

4. Install jiq

   go get github.com/fiatjaf/jiq/cmd/jiq #required sudo in Ubuntu 18.04 WSL 
   export PATH=$PATH:~/go/bin            #location where go-golang Ubuntu package installs go-lang binaries

jmespath.terminal

This is jmespath.terminal git project without latest activity but still works.

jq

Turn JSON into CSV

.Snapshots[] | [.Description,.Encrypted,.VolumeId,.State,.VolumeSize,.StartTime,.Progress,.OwnerId,.SnapshotId] | @csv
# by using filter '.Snapshots[]' you select "Snapshots" array every object, then
# create an array with [.key1,.key2] as array is required data structure to turn into CSV
# by default double quote will be escaped ' \" ' using '-r' prevents doing it making easier to open in Excel.

Json blob

{
    "Snapshots": [
        {
            "Description": "Copied for DestinationAmi ami-85921111 from SourceAmi ami-e3045222 for SourceSnapshot snap-0ebc77af111b09222. Task created on 1,487,109,468,219.",
            "Encrypted": false,
            "VolumeId": "vol-ffffffff",
            "State": "completed",
            "VolumeSize": 8,
            "StartTime": "2017-02-14T21:57:52.000Z",
            "Progress": "100%",
            "OwnerId": "3335812595337",
            "SnapshotId": "snap-58862222"
        },
        {
            "Description": "Copied for DestinationAmi ami-ccc875a8 from SourceAmi ami-cfda333 for SourceSnapshot snap-71347333. Task created on 1,485,867,366,917.",
            "Encrypted": false,
            "VolumeId": "vol-ffffffff",
            "State": "completed",
            "VolumeSize": 60,
            "StartTime": "2017-01-31T12:56:11.000Z",
            "Progress": "100%",
            "OwnerId": "222557238111",
            "SnapshotId": "snap-2314333"
        }
    ]
}

Filtered list of instance name, type, virtualization type and instance id

At the beginning my simple convention for Json is [] is an array and {} is an object.

Get an JSON output of all instances in your AWS accounty

aws ec2 describe-instances --output json | jiq #pipe into jiq

#jiq's expressions line
Filter]> .Reservations[].Instances[] | {_name: .Tags[], _id: .InstanceId, _type: .InstanceType, _virtType: .Hypervisor }

.Reservations[].Instances[] returns all data that we need, therefore we pipe into new object { _key1: XXX, _key2: YYY }. Actually, the keys fields we define now, as: _name, _id, _id, _type.

Next, let's select only Tags we are interested in, from a list(object). Therefore, we pipe Tags via select function. Where the Tag name is Name and we want get .Value field.

{name: .Tags[] | select(.Key=="Name").Value

Full filter command:

[Filter]> .Reservations[].Instances[] | {name: .Tags[] | select(.Key=="Name").Value, id: .InstanceId, type: .InstanceType, virt_type: .Hypervisor }

{                              
  "_name": "box-2.prod1.example.com",
  "_id": "i-ecccc666",                                                          
  "_type": "c4.2xlarge",
  "_virt_type": "xen"   
}                            
{           
  "_name": "db-2.prod1.example.com",
  "_id": "i-0aaaaae989a68b6e6",                     
  "_type": "m4.large",                        
  "_virt_type": "xen"                          
}

Now, we want to display only records, that match our instance name using REGEX expression .*box.*

{_name: .Tags[] | select(.Key=="Name").Value | match(".*box.*")

[Filter]> .Reservations[].Instances[] | {_name: .Tags[] | select(.Key=="Name").Value | match(".*box.*") , _id: .InstanceId, _type: .InstanceType, _virt_type: .Hypervisor }

{                                                                                                                                                           
  "_name": {                                                                                                                                          
    "offset": 0,                                                                                                         
    "length": 25,                                                                                                                                  
    "string": "box-2.prod1.example.com",
    "captures": []       
  },                           
  "_id": "i-0c26072b64b6404dc",                            
  "_type": "m4.large",                                                          
  "_virt_type": "xen"   
}                      
{                            
  "_name": { 
    "offset": 0,                                         
    "length": 24,                                  
    "string": "db-2.prod1.example.com", 
    "captures": []                            
  },                         
  "_id": "i-0a6f8fe989a68b6e6",    
  "_type": "m4.large",                                           
  "_virt_type": "xen"                          
}

The output contains now extra info. Butm but we are only interested in string field

{_name: .Tags[] | select(.Key=="Name").Value | match(".*box.*").string

Let's sort out the output by _id field. Note, only arrays [] aka lists can be sorted using sort_by</function>. That's why we need to wrap our object {} into brackets [].

[Filter]> [.Reservations[].Instances[] | {_name: .Tags[] | select(.Key=="Name").Value | match(".*box.*").string, _id: .InstanceId, _type: .InstanceType, _virt_type: .Hypervisor }] | sort_by(._id)

Real use:

$ aws ec2 describe-instances --filters "Name=tag:Name,Values=*mft*" "Name=instance-state-code,Values=16" | jq -r '[.Reservations[].Instances[] | {_name: .Tags[] | select(.Key=="Name").Value | match(".*box.*").string, _id: .InstanceId, _type: .InstanceType, _virt_type: .Hypervisor }] | sort_by(._id)'

Awscli queries

$ aws ec2 describe-instances \
  --output table \
  --query 'Reservations[].Instances[].[Tags[?Key==`Name`] | [0].Value,InstanceId,InstanceType]'

And we get a nicely formatted table:
-----------------------------------------------
|              DescribeInstances              |
+----------------+--------------+-------------+
|  xxxxxxxxxx    |  i-a0169xxx  |  r3.large   |
|  yyyyyyyyyy    |  i-11a46xxx  |  m3.large   |
|  zzzzzzzzzzzzzz|  i-07c4axxx  |  t2.medium  |
+----------------+--------------+-------------+

References

• jq and json training
• Jq manual Include functions
• jqplay Jq playground online querying tool