Extract with JSON Path
Infoveave Data Automation — JSON & Array
Your API response column stores nested JSON. You need just the user name or the order total. Write one JSONPath expression — Infoveave reads every row and extracts exactly what you need.
Many data sources — REST APIs, event logs, webhook payloads, and IoT platforms — deliver data with nested JSON objects stored as string columns in otherwise tabular records. Flattening these semi-structured columns to extract specific fields for reporting, filtering, and joining requires PARSE_JSON and colon-operator expressions in Snowflake, JSON_VALUE or JSON_EXTRACT in MySQL/BigQuery, jq scripts in command-line pipelines, or json.loads plus key navigation in Python. All of these require knowing the exact nesting structure and writing code or query expressions. Extract with JSON Path brings this capability into the no-code pipeline using the standard JSONPath syntax, so data engineers can point at any nested key using a readable path expression and retrieve its value into a flat column on every pipeline run. Multiple Extract with JSON Path steps can be chained to flatten multiple fields from the same JSON column without duplicating data.
What Extract with JSON Path does
Definition
Extract with JSON Path is an Infoveave transformation that parses a JSON string column row by row, evaluates a JSONPath expression (for example $.user.name), and writes the result into a new flat column for reporting, joins, and filters.
Extract specific values from JSON string columns using JSONPath expressions in Infoveave. Pull nested keys, array elements, and deep object values from semi-structured JSON data into flat output columns without PARSE_JSON queries, jq commands, or custom Python parsing code.
Part of Infoveave Data Automation on the Unified Data Platform.
When to use Extract with JSON Path
- Your dataset has a column that stores the full API response or webhook payload as a JSON string and you need to extract specific fields — such as status, amount, user ID, or nested metadata — into separate flat columns for analysis
- You are ingesting event tracking data where an EventPayload column contains nested JSON with event properties, and you need to extract individual properties into columns for use in filtering, grouping, and metric calculations
- You are processing IoT sensor records where a Readings column stores sensor measurement values nested inside a JSON object and you need to extract individual sensor metrics as flat numeric columns
- You are building a data flattening pipeline where structured data arrives in JSON format and must be normalized into a relational schema with one column per attribute for loading into a reporting database or visualization tool
When to avoid it
- The column contains pure array JSON (a top-level array rather than an object) and you need to extract an element by index or slice a sub-array — use Extract from Array for positional array element access
- You need to create a new JSON object by combining multiple existing columns into nested JSON format rather than extract from existing JSON — use Create JSON Object for building JSON from columns
- The column is not JSON but is another semi-structured format such as XML, CSV-in-a-cell, or a delimited string — use an appropriate parsing or splitting transformation instead
Where it fits in your Infoveave automation
Extract with JSON Path is one step inside a multi-step Infoveave workflow. Chain it with other activities — no code, no manual hand-offs.
Build this workflow visually in Infoveave Data Automation — drag, connect, and schedule with no infrastructure setup.
How teams use Extract with JSON Path
Real scenarios where this transformation saves hours of manual work.
Extract Order Total and Status from E-Commerce API Payload Column
A retail integration team ingests order data from an e-commerce platform API where each record includes a full JSON response payload in a Payload column. The team needs the order total and fulfillment status for revenue reporting. Extract with JSON Path uses $.order.total and $.order.status to pull these values into OrderTotal and OrderStatus columns for direct use in dashboards without any manual JSON decoding.
Pull Sensor Temperature Readings from Nested IoT JSON Messages
A manufacturing plant ingests IoT sensor messages where each record has a MessageBody JSON column containing nested sensor readings. The temperature reading lives at the path $.sensors.temperature.value. Extract with JSON Path extracts this into a TemperatureReading column for real-time and historical process monitoring. A downstream step applies a numerical range filter to alert on out-of-spec temperature values.
Extract Transaction Metadata from Banking Event Logs
A financial analytics team processes bank transaction event logs where each record has an EventData JSON column storing transaction metadata including merchant category, channel, and device type. Extract with JSON Path pulls $.transaction.merchant.category into MerchantCategory and $.transaction.channel into TransactionChannel for spend categorization analysis and fraud pattern detection.
See Extract with JSON Path in action
Input data (left) is transformed using the configuration below. The output table (right) is ready for dashboards or downstream steps.
EventDataUserName$.user.nameYesInput Data
| UserID | EventData |
|---|---|
| 1 | {"user": {"name": "Alice", "tier": "gold"}, "order": {"total": 1250.00}} |
| 2 | {"user": {"name": "Bob", "tier": "silver"}, "order": {"total": 890.50}} |
| 3 | {"user": {"name": "Carol", "tier": "gold"}, "order": {"total": 3400.00}} |
| 4 | {"user": {"name": "Dave", "tier": "bronze"}, "order": {"total": 450.25}} |
| 5 | {"user": {"name": "Eve", "tier": "gold"}, "order": {"total": 2100.75}} |
Output Data
| UserID | EventData | UserName |
|---|---|---|
| 1 | {"user": {"name": "Alice", ...}} | Alice |
| 2 | {"user": {"name": "Bob", ...}} | Bob |
| 3 | {"user": {"name": "Carol", ...}} | Carol |
| 4 | {"user": {"name": "Dave", ...}} | Dave |
| 5 | {"user": {"name": "Eve", ...}} | Eve |
Configuration
Key fields to configure in the Infoveave workflow builder. Full reference available in the documentation.
Column Name
Select the column that contains JSON string values. Each row's value in this column will be parsed and the configured JSONPath expression will be evaluated against it. The column can contain different JSON structures per row — rows where the path does not exist produce a null in the output column.
Output Column
Enter the name for the new column that will store the extracted value. If the JSONPath resolves to a scalar value (string, number, boolean), that value is stored directly. If the path resolves to a nested object or array, the result is stored as a JSON string representation of that sub-structure.
JSON Path
Enter the JSONPath expression to navigate the JSON structure. Start with $ for the root. Use dot notation for object keys: $.user.name. Use bracket notation for array elements: $.items[0].price. Use nested dot paths for deeply nested values: $.response.data.order.amount. The expression is evaluated against each row's JSON independently.
Include Original
Choose whether to retain the source JSON column alongside the new extracted column. Keep it enabled when extracting multiple fields from the same JSON column across multiple chained steps, or when the full JSON is needed for audit. Disable to remove the JSON column after all required fields are extracted.
Frequently asked questions
Everything you need to know about Extract with JSON Path in Infoveave.
Also in JSON & Array — and what runs before & after
Transformations in the same family as Extract with JSON Path, often chained together in the same Infoveave workflow.
Part of Infoveave Data Automation
80+ transformations. Zero manual steps.
Extract with JSON Path is one of over 80 transformation activities available inside Infoveave workflows. Chain transformations together — no code, no exports, no waiting for IT.
Ready to see Infoveave in action?
