The accompanying diagram demonstrates the challenge of processing shipment tracking records within a logistics platform. It showcases how the Event Source node reads the data but treats fields like tracking history and items as stringified JSON. This results in these fields being handled as plain strings rather than structured JSON objects. The diagram also highlights how the Transform node is unable to extract meaningful insights from these fields, ultimately showing that the JSON data remains unprocessed and inaccessible for detailed analysis.

The result below showcases how stringified JSON fields appear as plain strings, making it challenging to extract values without JSON_QUERY.

tracking_id	status	origin	destination	tracking_history	items	failure_reason	retry_attempts	event_time
ABC123XYZ	In Transit	New York, USA	Los Angeles, USA	[{"date":"2025-02-05","location":"New York, USA","status":"Picked Up"},{"date":"2025-02-06","location":"Philadelphia, USA","status":"In Transit"},{"date":"2025-02-07","location":"Chicago, USA","status":"Arrived at Facility"}]	[{"item_id":"12345","name":"Laptop","quantity":1},{"item_id":"67890","name":"Wireless Mouse","quantity":2}]	null	null	2025-02-09 10:55:34.359

Business & Technical Impact

• Without JSON_QUERY, developers must implement complex string manipulation techniques, increasing development effort and maintenance overhead.
• Business users face difficulties extracting actionable insights from nested JSON data, limiting operational efficiency.
• Real-time data processing is slowed, affecting timely decision-making and business analytics.

JSON_QUERY® in Apache Flink simplifies structured data extraction from JSON stored as a string. While available in earlier versions, Flink 1.20 introduces the RETURNING clause, enhancing flexibility for both business users and developers.

Syntax:

JSON_QUERY(jsonValue, path
  [RETURNING dataType] 
  [{WITHOUT | WITH CONDITIONAL | WITH UNCONDITIONAL} [ARRAY] WRAPPER] 
  [{NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR} ON EMPTY] 
  [{NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR} ON ERROR]
)

• jsonValue – The input JSON stored as a string.
• path – Defines the JSON path expression to extract specific data.
• RETURNING dataType – (New in Flink 1.20) Specifies the output type as STRING or ARRAY<STRING>.
• WRAPPER options – Controls how the result is wrapped:
  • WITHOUT ARRAY WRAPPER: Returns the extracted value as is, without wrapping it in an array. If the result is an array, it remains unchanged.
  • WITH CONDITIONAL ARRAY WRAPPER: Wraps the extracted value in an array only if it is not already an array.
  • WITH UNCONDITIONAL ARRAY WRAPPER: Always wraps the extracted value in an array, even if it is already an array.
  • ARRAY is optional: Including or omitting ARRAY in the WRAPPER clause does not change the result.
• ON EMPTY & ON ERROR – Defines the behavior when no match is found or an error occurs:
  • ON EMPTY: Specifies the result when the JSON path expression evaluates to an empty value. Options are returning NULL (default), an empty object ({}), an empty array ([]), or raising an error.
  • ON ERROR: Determines the behavior when an error occurs during path evaluation. It can return NULL (default), an empty object ({}), an empty array ([]), or raise an error.
  • Both parameters provide error handling and missing data management to ensure smooth JSON processing.

Key Business Benefits

✔ Simplifies Data Processing – Extracts nested JSON values effortlessly, reducing development effort.
✔ Enhances Flexibility – Adapts to dynamic JSON structures, enabling seamless integration into event-driven workflows.
✔ Optimizes Performance – Eliminates manual string operations, improving real-time data processing speed.

Extracting Shipment Insights with JSON Query

JSON_QUERY streamlines data extraction, enabling business users to define precise paths for retrieving structured data effortlessly. The json_query method can be seamlessly integrated into the Transform node using a Custom SQL Processor node, ensuring efficient and flexible event processing.

JSON_QUERY(tracking_history, '$[*].date') RETURNING ARRAY<STRING>

Extracts all dates from tracking_history and returns them as an array of strings.

JSON_QUERY(tracking_history, '$[0].location') RETURNING STRING

Retrieves the first recorded location from tracking_history (e.g., "New York, USA").

JSON_QUERY(items, '$[*].name') RETURNING ARRAY<STRING>

Extracts all item names from the items field as an array of strings.

The diagram below demonstrates how JSON_QUERY is applied within the Transform node using a Custom SQL Processor. This enables seamless extraction of structured data from stringified JSON, allowing businesses to efficiently access and utilize key insights in real time.

The results below showcase the successful extraction of structured data from stringified JSON. The transaction history and items fields, previously stored as strings, are now parsed, enabling seamless access to key insights for real-time processing.

tracking_id	status	origin	destination	dates	location	itemNames	statuses	failure_reason	retry_attempts	event_time
ABC123XYZ	In Transit	New York, USA	Los Angeles, USA	["2025-02-05","2025-02-06","2025-02-07"]	["New York, USA"]	["Laptop","Wireless Mouse"]	["Picked Up","In Transit","Arrived at Facility"]	null	null	2025-02-09 12:19:35.487

JSON Query ensures smooth processing even when JSON data is missing or malformed.

The behavior of empty JSON results can be controlled using the ON EMPTY parameter, which allows returning NULL, {}, [], or raising an error.

Similarly, the ON ERROR parameter lets users define the response to path evaluation errors by returning NULL, {}, [], or raising an error.

Example 1: Handling Empty Fields

If the tracking_history field is missing or empty, JSON_QUERY returns an empty array ([]) instead of causing errors, ensuring seamless data processing.

Example 2: Handling Invalid JSON Formats

When a corrupted JSON string is received, JSON_QUERY can return null or a predefined error message based on the configuration, preventing disruptions in data processing.

See how JSON_QUERY effectively handles empty and invalid JSON fields, maintaining smooth and error-free data processing.