How to extract text from a JSON array in SSIS

SSIS PowerPack JSON
1

Introduction

In data integration tasks, working with JSON data is often required. SSIS (SQL Server Integration Services) provides powerful tools through the ZappySys SSIS PowerPack to handle JSON data efficiently. One common requirement is to extract text from a JSON array using JSON Path expressions. The FUN_JSON_TO_TEXT function is perfect for this task, allowing you to specify custom path expressions to extract and format JSON values into readable text.

In this article, we’ll demonstrate how to use the FUN_JSON_TO_TEXT function to extract values from JSON arrays and format them with custom separators such as spaces, semicolons, or newlines.

Prerequisites

  • SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from this site.

Steps

We’ll use the function FUN_JSON_TO_TEXT, which extracts text from a JSON array using a JSON Path expression. You can specify an expression like $.data[*] or $.data[*].email. If the document is an array (i.e., starts with [), then the root is referred to as $.data[*].

  1. Drag and drop a Data Flow Task into your SSIS package.

  2. drag a valid source component inside the Data Flow that contains JSON values in one of its columns. For this example, we’ll use an XML Source, with the following XML data:

    <example>
  <json1>['A','B','C']</json1>
  <json2>['X','Y','Z']</json2>
  <json3>[{id:'10'},{id:'20'},{id:'30'}]</json3>
</example>

    Each JSON element will be extracted differently:

    • json1: Values will be separated by a space.
    • json2: Values will be separated by a semicolon.
    • json3: Values will be separated by a newline (\n).

    XML Source Example
    XML Source Example819×594 33.6 KB

  3. Connect a Template Transform Component to the JSON source. This is where we will apply the FUN_JSON_TO_TEXT function.

    Here’s the function syntax:

    <<input_json|~|json_path[|~|separator],FUN_JSON_TO_TEXT>>
    • input_json: [String] The JSON document from which to extract text.
    • json_path: [String] The JSONPath filter expression.
    • separator (Optional): [String] A separator string to join multiple matches. Use \r (carriage return), \n (linefeed), \r\n (newline), \t (tab). The default separator is a space.

    The examples we will use:

    Example 1: <<<%example.json1%>|~|$.data[*],FUN_JSON_TO_TEXT>>
Example 2: <<<%example.json2%>|~|$.data[*]|~|;,FUN_JSON_TO_TEXT>>
Example 3: <<<%example.json3%>|~|$.data[*].id|~|\n,FUN_JSON_TO_TEXT>>

    Extract Text from JSON
    Extract Text from JSON838×589 43.3 KB

  4. Connect a destination component (e.g., Trash Destination, SQL Server Destination) and run the package.

    Package Result

  5. Here’s the result after extracting and formatting the JSON data:

    Extract Result

Video Tutorial

Conclusion

In this article, we explored how to use the FUN_JSON_TO_TEXT function from the ZappySys SSIS PowerPack to extract and format text from JSON arrays using JSON Path expressions. This powerful function allows you to easily extract JSON data and format it with custom separators, making it simple to work with complex JSON structures. Should you face any issues or need further assistance, our support team is ready to help via chat on our website

References