how to get flat output using JMESPath query

ONLINECAST

2 min read

·

01-09-2024

15

0

Share

Flattening JSON Data with JMESPath: A Practical Guide

JMESPath is a powerful query language designed for extracting data from JSON documents. While it excels at navigating nested structures, sometimes we need to flatten the output into a more tabular or CSV-like format. This article will explore how to achieve this using JMESPath, drawing inspiration from a real Stack Overflow question.

The Challenge:

Let's consider a scenario where we have a JSON document containing information about web applications and their hostnames with associated SSL states:

[
    {
        "hostNameSslStates": [
            {
                "name": "exmple11.com",
                "sslState": "SniEnabled"
            },
            {
                "name": "example22.com",
                "sslState": "Disabled"
            },
            {
                "name": "example23",
                "sslState": "Disabled"
            }
        ],
        "name": "FirstWebApp"
    },
    {
        "hostNameSslStates": [
            {
                "name": "xyz.com",
                "sslState": "SniEnabled"
            },
            {
                "name": "xyz2.com",
                "sslState": "SniEnabled"
            },
            {
                "name": "zyx23.com",
                "sslState": "Disabled"
            }
        ],
        "name": "SecondWebApp"
    }
]

Our goal is to extract only the hostnames with "SniEnabled" SSL state and present them in a flat, CSV-like format:

FirstWebApp exmple11.com SniEnabled
SecondWebApp xyz.com SniEnabled
SecondWebApp xyz2.com SniEnabled

The Solution:

To achieve this flattening, we'll leverage JMESPath's powerful features:

1. Filtering: We'll use the [] operator to filter the hostNameSslStates array based on the "sslState" field.
2. Projection: We'll use the . operator to access the desired fields (name and sslState) for each hostname.
3. Concatenation: We'll use the + operator to join the relevant data into a single string for each hostname.

Here's the JMESPath query that accomplishes this:

[
  * | {
    "name": name, 
    "hostnames": hostNameSslStates[?sslState == `SniEnabled`] | [
      {
        "name": name,
        "sslState": sslState
      }
    ] | [_.name + " " + _.sslState]
  }
] | flatten(@)

Explanation:

1. Outer []: This represents an array of objects, where each object corresponds to a web application.
2. *: This iterates through each object in the input array.
3. | { ... }: This creates a new object for each web application, containing its "name" and filtered hostnames.
4. hostNameSslStates[?sslState == SniEnabled]: This filters the hostNameSslStates array, keeping only entries where sslState is "SniEnabled".
5. [ { ... } ]: This creates a new array with filtered hostnames, containing the "name" and "sslState" for each hostname.
6. | [_.name + " " + _.sslState]: This iterates through the filtered hostname array, concatenating the "name" and "sslState" fields separated by a space, creating the desired flat output.
7. | flatten(@): This flattens the resulting array of arrays into a single array, removing nested structures.

Additional Notes:

• This solution focuses on demonstrating flattening using JMESPath. You can use different output formats like CSV or JSON depending on your needs.
• For more complex data transformations, you might need to explore additional JMESPath features like map() or reduce().
• Consider using libraries like jmespath in Python or jq for command-line interaction with JMESPath.

Conclusion:

By leveraging JMESPath's filtering, projection, and concatenation capabilities, you can efficiently flatten JSON data and extract specific information in a desired format. This technique can be invaluable for data processing and analysis tasks, particularly when working with structured JSON data. Remember to explore the full range of JMESPath operators and functions to tailor your solutions to specific requirements.