JSONPath Expression Examples – JSON Cheatsheet (SSIS / ODBC)

What is JSONPath expression

JSONPath expression is an expression language to filter JSON Data. Its a similar concept to XPath expression in XML but has limited features compared to XPath. If you are extracting data from REST API Services using SSIS JSON / XML Source or ODBC JSON / XML Driver then you will quickly realize that it’s very important to extract nested data by navigating to a certain level.

 

Sample data for JSONPath expression

All examples of JSONPath expression in this post will use the following sample JSON for reference.

JSONPath Examples

JSON Path – Using Regular Expressions (Regex)

The new version of SSIS PowerPack and ODBC PowerPack Supports Regular expressions in the path. Learn more about Regular expressions check this article

JSON Path – Nested Scan for any level (double dots)

Now lets look at example of below JSON. Assume that you like to extract information from all nested “children”  nodes (basically extract id and name of every child from any level).

You can use double dots without specifying parent heirarchy like below to scan recusrsivly.

$..children[*] For this you can write following JSON Path

$..children[*]

OR (below to find all children where name is c1 or c2 or c3)

$..children[?(@name=~ /c1|c2|c3/ )]

Here is an example of a JSON Driver in ODBC. The same expression can be used in SSIS too.

ODBC JSON Driver - Using Filter with Regular Expressions / JSONPath

ODBC JSON Driver – Using Filter with Regular Expressions / JSONPath

JSONPath for Root Objects in Array

Now lets look at another pattern of JSON where your documents are on the first level rather than under some array property.

Example JSON

Example JSON Path Filters

 

 

Posted in SSIS JSON Source (File/REST), SSIS Tips & How-Tos and tagged , , , , , , , .