Using JSONPath Queries on JSON Data

JSON data for querying with JSONPath

JSONPath does for JSON processing what XPath (defined as a W3C standard) does for XML. JSONPath queries can be super useful, and are a great addition to any developer or ops person’s toolbox.

You may want to do a quick data query, test, or run through some JSON parsing scenarios for your code. If you have your data easily available in JSON format, then using JSONPath queries or expressions can be a great way to filter your data quickly and efficiently.

JSONPath 101

JSONPath expressions use $ to refer to the outer level object. If for example you have an array at the root, $ would refer to that array.

When writing JSONPath expressions, you can use dot notation or bracket notation. For example:

  • $.animals.land[0].weight
  • $['animals']['land'][0]['weight']

You can use filter expressions to filter out specific items in your queries. For example: ?(<bool expression>)

Here is an example that would filter our collection of land animals to show only those heavier than 50.0, returning their names:

$.animals.land[?(@.weight > 50.0)].name

The wildcard character * is used to select all objects or elements.

Note the @ symbol that is used to select the ‘current’ item being iterated in the boolean expression.

There are more JSONPath syntax elements to learn about, but the above are what I find most useful and commonly required.

JSONPath Query Example

Here is a chunk of JSON data, and some basic queries that show how you can easily filter down the dataset and select what you need.

JSONPath Queries – Example 1

Find all “Report runs” where root.id is equal to a specific value:

$.runs[?(@.root.id=="af1bcd6b-406f-43f9-86b3-9f01ee211ddc")]

JSONPath Queries – Example 2 (AND operator)

Find all “Report runs” where root.id is equal to a specific value, and shell.id is equal to a specific value:

$..runs[?(@.root.id=="af1bcd6b-406f-43f9-86b3-9f01ee211ddc" && @.shell.id=='d743537e393d')]

Useful JSONPath Resources

Use this webapp to write and test JSONPath expressions live in your browser.

vSphere 6.0 performance metric limitations in the database (config.vpxd.stats.maxQueryMetrics)

A change I noticed right away between vSphere 5.5 and vSphere 6.0 is the introduction of a default limiter when it comes to performing database queries for performance metrics.

When querying vCenter 6.0 for performance data, there is a system in place by default that limits the number of entities that are included in a database query. As performance charts in the vSphere Web and C# client depend on this performance data, you may sometimes see an error when attempting to view overview or advanced charts because of this change.

In my case, I am using some custom code to query performance metrics using vSphere APIs and noticed the issue right away, as I was trying to gather a large amount of data.

VMware state that the reason for the change is to protect the vCenter database from receiving intensive or large queries.

If you wish to work around this, or remove the limit, you’ll need to introduce a new key/value pair advanced setting in the advanced settings area for your vCenter server instance. The key should be named “config.vpxd.stats.maxQueryMetrics” (without the quotes) and should have a value set of -1 to disable the limit. This could also be set to a value of 100 for example to limit the entities included in a database query to 100.

A further edit should be made to the web.xml file, however in my case I was not concerned with the limit affecting the client, as I was using the API, and making the first change seemed to do the trick for me.

You can read more about this setting by using this link to the official VMware KB article

A quick way of finding out where your FSMO roles reside

 

A nice and simple blog post today, based on finding out where your FSMO roles lie, using just the command prompt. This is useful in a couple of different situations, namely:

 

  • You don’t want to spend a long time using MMCs / Active Directory Users and Computers to figure out where each of the FSMO roles are.
  • You don’t have easy access to MMCs – for example you are using Windows Server 2008 Core

 

This command works on both Windows Server 2003 as well as Server 2008 / R2.

Simply type the following in your command prompt window on one of your domain controllers:
netdom query fsmo

 

Your output should be something like the following, listing the servers which hold each FSMO role.