How to convert the output of Google Cloud Asset Query to JSON or Python

In this blog, I will show you how you can convert the output of a Google Cloud Asset query into JSON format using the command line, or to an Python object in a Python program. This transformation will streamline your workflow and make reporting and data handling easier. I will show you how to do this from the command line and from within a Python program.

Google Cloud Asset Query output

The problem with the output of the Google Cloud Asset Query, is that the output is not a Python or JSON object. Although the metadata shows the proper shape and types of the result, that shape is not returned. Instead the result consists of an object with field names f and v where all values are in string format. As shown below:

gcloud asset query \
    --project $(gcloud config get core/project) \
    --format json \
    --statement 'select distinct(assetType) as type from STANDARD_METADATA'

{
  "queryResult": {
    "nextPageToken": "",
    "rows": [
      {
        "f": [
          {
            "v": "compute.googleapis.com/Network"
          }
        ]
      },
      {
        "f": [
          {
            "v": "secretmanager.googleapis.com/SecretVersion"
          }
        ]
      }
      ...
    ],
    "schema": {
      "fields": [
        {
          "field": "type",
          "mode": "NULLABLE",
          "type": "STRING"
        }
      ]
    },
    "totalRows": "43"
  }

Command line usage

On the command line you can use the google-asset-query-converter to convert the result into proper JSON. The utility uses the schema definition that is returned with the result to do the conversion as shown below:

gcloud asset query \
    --project $(gcloud config get core/project) \
    --format json \
    --statement 'select distinct(assetType) as type from STANDARD_METADATA' | \
    google-asset-query-converter --pretty-print

[
  {
    "type": "compute.googleapis.com/InstanceGroup"
  },
  {
    "type": "appengine.googleapis.com/Application"
  },
  {
    ..
  ]

Now it is easier to process the query results without tools like jq:

gcloud asset query \
    --project $(gcloud config get core/project) \
    --format json \
    --statement 'select distinct(assetType) as type from STANDARD_METADATA' | \
    google-asset-query-converter --pretty-print | \
    jq 'map(.type)'

[
  "appengine.googleapis.com/Application",
  "compute.googleapis.com/InstanceGroup",
  "pubsub.googleapis.com/Topic",
  "iam.googleapis.com/ServiceAccount",
  "compute.googleapis.com/Disk",
  "bigquery.googleapis.com/Table",
  ...

For this query you could process the output directly from the query with jq, but I dare you to process the output of select * from compute_googleapis_com_Instance directly with jq :-p

Library Usage

You can also use the converter to process the results of a Cloud Asset Query in Python as well. This is shown in the snippet below:

from googleapiclient.discovery import build
from google_asset_query_converter.converter import PythonConverter
from google_asset_query_converter.queryresult import AssetQueryResponse


def print_metadata(project:str):
    service = build("cloudasset", "v1")
    request = service.v1().queryAssets(
        parent=f'projects/{project}',
        body={"statement":  "SELECT * FROM STANDARD_METADATA",
              "pageSize": 20
              }
    )

    converter = PythonConverter()
    response = AssetQueryResponse(**request.execute())
    for row in response.query_result.rows:
        print(converter.row(response.query_result.table_schema, row))

So that is all there is to it: just install it and off you go!

Installation

To install the google-asset-query-converter, type:

pip install git+https://github.com/xebia/google-cloud-asset-converter.git@0.0.3

It requires Python 3.12 or later.

Conclusion

This utility provides an easy way to convert the output of a Google Cloud Asset Query to JSON or a Python object for further processing.

Image by S. Nagel from Pixabay