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. Root filter only works if you have a version released after Dec-29-2021. Previous version didn’t support filtering on Root Array.

 

Example JSON

Example JSON Path Filters

NOTE: using two dots after $ (e.g. $..[zzzz]) invokes nested scan so any level array will return documents, this may not be the acceptable case sometimes but if you want to filter the root array then this is the only way for now in ZappySys.

If you must not allow recursive scan then you can use the following two properties

EnableRawOutputModeSingleRow = true;
RawOutputDataRowTemplate = “{data: [$1] }”  –where [$1] is replaced at runtime with Document Text you like to parse (can be static or URL response)

OR

Use functions in RawOutputDataRowTemplate if needed

RawOutputDataRowTemplate = “{data: <<[$1],FUN_BASE64DEC>>}”

The above will wrap around the actual JSON / XML you need to parse so now after this setting you can use JSONPath = $.data[?(@id==1)] to avoid two dots to prevent a recursive scan

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