Introduction
Filtering data in JSON format is a common requirement when working with APIs and structured data sources in SQL Server Integration Services (SSIS). The ZappySys JSON Source component allows you to efficiently fetch, parse, and filter JSON data. This article will guide you through the process of filtering JSON data using JSONPath expressions in SSIS.
Prerequisites
- Download and install the ZappySys SSIS PowerPack
Steps
- Add a Data Flow Task to your SSIS package.
- Drag and drop a JSON Source component into the Data Flow. Provide a valid URL or local JSON file. Here is the JSON sample we will use in our example:
{
"store": {
"employees": [
{
"name": "bob",
"hiredate": "2015-01-01"
},
{
"name": "sam",
"hiredate": "2015-01-02"
},
{
"name": "ken",
"hiredate": "2015-01-03"
}
],
"books": [
{
"category": "reference",
"author": "bob",
"title": "hellooo1",
"price": 1.95,
"old_price": 2.96,
"sections": [
"s1",
"s2",
"s3"
]
},
{
"category": "fiction",
"author": "sam",
"title": "hellooo2",
"price": 1.96,
"old_price": 0.96,
"sections": [
"s4",
"s1",
"s3"
]
},
{
"category": "science",
"author": "steve",
"title": "hellooo3",
"tag": "1bcd",
"price": 11,
"old_price": 10,
"sections": [
"s9",
"s2",
"s3"
]
}
],
}
}
- Now, in the Array filter, we can use JSONPath to filter our data.
Below you will find common JSON filtering scenarios.
Filtering scenarios
Selecting an array
-
To retrieve all employees:
$.store.employees[*]
-
To retrieve all books:
$.store.books[*]
Filtering array on a single condition
-
To filter employees hired after
2015-01-01, use:
$.store.employees[?(@.hiredate > '2015-01-01')]
-
To filter books with price greater than 2:
$.store.books[?(@.price > 2)]
Filtering array on multiple conditions
-
To filter books which contains section s4 or s9 (logical OR operator - “||”)
$.store.books[?(@.sections[*]=='s4' || @.sections[*]=='s9' )]
-
To filter books where first section is s1 and second section is s2 (logical AND operator - “&&”)
$.store.books[?(@.sections[0]=='s1' && @.sections[1]=='s2')]
Filtering array using Regular Expressions
For more details, refer to this link:
(Using Regular Expressions in SSIS | ZappySys Blog)
-
To filter books where author attribute contains ‘Nigel’ or ‘Waugh’
$.store.books[?(@author=~ /Nigel|Waugh/ )] -
To filter books where category is ‘reference’ OR author attribute contains ‘Nigel’ or ‘Waugh’
$.store.books[?(@category=='reference' || @author=~ /Nigel|Waugh/ )]
Filtering with the nested scan for Any Level option
To extract information from all nested sections nodes: $...sections[*]
Filtering using the JSONPath for Root Objects in an Array
If your JSON documents are at the first level rather than under an array property, you can use: We will work with the public API URL: https://jsonplaceholder.typicode.com/users
-
Get the first two documents
$.[:2]
-
Extract all documents where Username=Bret and ID = 1. Use two dots after $ for filter
$..[?(@username=='Bret' && @id==1)]
Conclusion
Filtering JSON data efficiently using ZappySys JSON Source and JSONPath expressions in SSIS helps streamline data processing, reducing unnecessary data loads and improving overall performance. By applying targeted filters, you can extract only the required information, leading to optimized workflows and faster data transformations.
JSONPath expressions offer powerful filtering capabilities, including conditional selections, nested scans, and regular expressions, making them a versatile tool for handling complex JSON structures. Whether working with APIs, local JSON files, or large datasets, mastering JSON filtering can significantly enhance your data integration processes.
To get started, download and install the ZappySys SSIS PowerPack and begin refining your JSON extraction techniques. If you have any questions or require further assistance, feel free to contact ZappySys Support.
References
Contact us
If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.