davidzollo opened a new issue, #11249:
URL: https://github.com/apache/seatunnel/issues/11249
## Search before asking
- [x] I searched the existing issues and found related topics such as #11061
and #1917, but they do not cover this exact request.
## Description
### Background
The current SQL Transform in SeaTunnel is backed by the existing Zeta SQL
engine. It does not provide a built-in JSON path extraction function such as
`GET_JSON_OBJECT(json_col, '$.key1')`.
At the moment, SeaTunnel users can only work around this by adding a
separate `JsonPath` transform before the SQL transform, then referencing the
extracted temporary field in SQL.
That workaround exists, but it makes migration from Hive/Spark-style SQL
significantly harder because many existing jobs already use JSON extraction
inline inside SQL expressions.
### Example
A real migration case needs to support SQL like this:
```sql
SELECT CASE
WHEN GET_JSON_OBJECT(PATH, '$.key1') = 'value1' THEN TRIM(key1)
ELSE TRIM(key1)
END AS new_col
FROM xxx
```
Today this has to be rewritten into multiple transforms and intermediate
columns.
### Why this is not a duplicate of existing issues
- #11061 proposes a new Calcite Transform with native JSON functions such as
`JSON_VALUE` and `JSON_QUERY`.
- That issue is about a new transform plugin, not the current SQL Transform.
- This request is specifically about improving the existing SQL Transform so
simple JSON extraction can be done inline without forcing users to migrate to
another transform type or split one SQL into multiple transforms.
### Proposal
Support one of the following in the existing SQL Transform:
1. Add a built-in `GET_JSON_OBJECT(json, path)` function for compatibility
with existing SQL jobs.
2. Or add an equivalent built-in JSON scalar extraction function and
document the compatibility / migration guidance clearly.
### Expected behavior
- The function should work inside `SELECT`, `CASE WHEN`, `WHERE`, and other
ordinary scalar-expression positions.
- Missing path / null input behavior should be defined clearly.
- Invalid JSON or invalid JSON path behavior should be documented.
### Current workaround
Use the standalone `JsonPath` transform first, then reference the extracted
field from SQL. This works, but it adds extra configuration and breaks direct
SQL portability.
### Acceptance criteria
- Support JSON path extraction in the current SQL Transform.
- Cover typical inline usages such as `SELECT`, `CASE WHEN`, and filter
conditions.
- Add unit tests for normal path extraction, null input, missing path, and
invalid JSON/path.
- Update docs with examples and limitations.
## Are you willing to submit a PR?
- [x] Yes I am willing to submit a PR!
## Code of Conduct
- [x] I agree to follow this project's Code of Conduct.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
