revised samples and skills

wilmaontherun · wilmaontherun · commit 7d56201d8cf9 · 2026-03-19T19:11:16.000-07:00
diff --git a/python/felderize/spark/cli.py b/python/felderize/spark/cli.py
@@ -173,12 +173,8 @@ def example(name: str | None, validate: bool, json_output: bool, no_docs: bool,
     if not name:
         click.echo("Available examples:\n")
         for ex_name, files in pairs.items():
-            if isinstance(files, Path):
-                preview = files.read_text().strip().split("\n")[0]
-                click.echo(f"  {ex_name:20s} {preview}  [combined]")
-            else:
-                preview = files[0].read_text().strip().split("\n")[0]
-                click.echo(f"  {ex_name:20s} {preview}")
+            tag = "[combined]" if isinstance(files, Path) else "[schema+query]"
+            click.echo(f"  {ex_name:20s} {tag}")
         click.echo("\nRun one with: felderize example <name>")
         return
 
diff --git a/python/felderize/spark/data/samples/aggregation_date_trunc.md b/python/felderize/spark/data/samples/aggregation_date_trunc.md
@@ -32,4 +32,3 @@ FROM orders WHERE status IN ('PAID', 'SHIPPED')
 GROUP BY region, TIMESTAMP_TRUNC(created_at, MONTH);
 ```
 
-Rewrites: `STRING`→`VARCHAR`, remove `USING parquet`, `CREATE OR REPLACE TEMP VIEW`→`CREATE VIEW`, `date_trunc('MONTH', x)`→`TIMESTAMP_TRUNC(x, MONTH)`.
diff --git a/python/felderize/spark/data/samples/array_lambda.md b/python/felderize/spark/data/samples/array_lambda.md
@@ -16,4 +16,3 @@ SELECT row_id, TRANSFORM(nums, x -> x + 1) AS nums_plus_one
 FROM collection_events;
 ```
 
-Rewrites: `ARRAY<INT>`→`INT ARRAY`. Lambda syntax preserved.
diff --git a/python/felderize/spark/data/samples/array_map_functions.md b/python/felderize/spark/data/samples/array_map_functions.md
@@ -32,4 +32,3 @@ SELECT user_id, CARDINALITY(tags) AS tag_count,
 FROM session_profiles;
 ```
 
-Rewrites: `ARRAY<STRING>`→`VARCHAR ARRAY`, `size()`→`CARDINALITY()`, `element_at(map, key)`→`map[key]`.
diff --git a/python/felderize/spark/data/samples/datediff.md b/python/felderize/spark/data/samples/datediff.md
@@ -14,4 +14,3 @@ Feldera:
 MAX(TIMESTAMPDIFF(DAY, shipped_at, delivered_at)) AS max_days
 ```
 
-Rewrites: `datediff(end, start)`→`TIMESTAMPDIFF(DAY, start, end)` (argument order reversed).
diff --git a/python/felderize/spark/data/samples/left_semi_join.md b/python/felderize/spark/data/samples/left_semi_join.md
@@ -20,4 +20,3 @@ INNER JOIN order_facts o ON c.customer_id = o.customer_id
 WHERE o.amount >= 500;
 ```
 
-Rewrites: `LEFT SEMI JOIN`→`INNER JOIN` + `SELECT DISTINCT`.
diff --git a/python/felderize/spark/data/samples/null_safe_equality.md b/python/felderize/spark/data/samples/null_safe_equality.md
@@ -14,4 +14,3 @@ Feldera:
 ON p.email <=> e.email AND p.phone <=> e.phone
 ```
 
-Rewrites: none — `<=>` is supported in Feldera.
diff --git a/python/felderize/spark/data/samples/nvl_coalesce.md b/python/felderize/spark/data/samples/nvl_coalesce.md
@@ -16,4 +16,3 @@ SELECT row_id, grp, COALESCE(amount, discount) AS resolved_amount
 FROM scalar_function_rows;
 ```
 
-Rewrites: `nvl(a, b)`→`COALESCE(a, b)`. Same for `ifnull(a, b)`.
diff --git a/python/felderize/spark/data/samples/row_number_topk.md b/python/felderize/spark/data/samples/row_number_topk.md
@@ -21,4 +21,3 @@ SELECT category, product_id, revenue FROM (
 ) ranked WHERE rn <= 3;
 ```
 
-ROW_NUMBER with TopK filter (`WHERE rn <= N`) passes through directly.
diff --git a/python/felderize/spark/data/skills/spark_skills.md b/python/felderize/spark/data/skills/spark_skills.md
@@ -255,7 +255,32 @@ Results are always `VARIANT` — cast to get a concrete type: `CAST(v['age'] AS
 | `json_array_length(s)` | `CARDINALITY(CAST(PARSE_JSON(s) AS VARIANT ARRAY))` | |
 
 Notes:
-- Parse once, reuse: `SELECT PARSE_JSON(col) AS v, CAST(v['key'] AS VARCHAR) AS k` — lateral column aliases supported.
+- **Parse once, reuse — for simple SELECT (no GROUP BY).** When extracting multiple fields from the same JSON column without aggregation, call `PARSE_JSON` once as a lateral alias:
+
+```sql
+-- Simple SELECT: parse once, reuse alias
+SELECT
+  PARSE_JSON(payload) AS v,
+  CAST(v['user_id'] AS VARCHAR)  AS user_id,
+  CAST(v['amount'] AS DOUBLE)    AS amount,
+  CAST(v['currency'] AS VARCHAR) AS currency
+FROM raw_events;
+```
+
+- **With GROUP BY: use a CTE to pre-parse.** The lateral alias pattern breaks with GROUP BY because `payload` must be in the GROUP BY or an aggregate. Pre-parse in a CTE instead. The CTE goes *inside* `CREATE VIEW ... AS`, not before it:
+
+```sql
+CREATE VIEW summary AS
+WITH parsed AS (
+  SELECT *, PARSE_JSON(payload) AS v FROM raw_events
+)
+SELECT
+  CAST(v['user_id'] AS VARCHAR) AS user_id,
+  COUNT(*) AS cnt
+FROM parsed
+GROUP BY CAST(v['user_id'] AS VARCHAR);
+```
+
 - For performance-critical paths, `CREATE TYPE` + `jsonstring_as_<type>` is more efficient than `PARSE_JSON` + bracket access.
 
 #### Math functions
@@ -590,10 +615,12 @@ SELECT id, UPPER(name) AS name, SOUNDEX(name) AS sound FROM users
 
 #### Bitwise (scalar)
 
-| Function | Notes |
-|----------|-------|
-| `bit_and(a, b)`, `bit_or(a, b)`, `bit_xor(a, b)` | Scalar 2-arg form not supported; only aggregate `BIT_AND/OR/XOR(col)` exists |
-| `shiftleft`, `shiftright`, `shiftrightunsigned` | No bitwise shift syntax in Feldera |
+| Spark | Feldera | Notes |
+|-------|---------|-------|
+| `bit_and(a, b)` | `a & b` | Use bitwise operator |
+| `bit_or(a, b)` | `a \| b` | Use bitwise operator |
+| `bit_xor(a, b)` | `a ^ b` | Use bitwise operator |
+| `shiftleft`, `shiftright`, `shiftrightunsigned` | — | No bitwise shift syntax in Feldera |
 
 #### Math
 

Original file line number	Diff line number	Diff line change
`@@ -32,4 +32,3 @@ FROM orders WHERE status IN ('PAID', 'SHIPPED')`
`32`	`32`	`GROUP BY region, TIMESTAMP_TRUNC(created_at, MONTH);`
`33`	`33`	```
`34`	`34`
`35`		-Rewrites: `STRING`→`VARCHAR`, remove `USING parquet`, `CREATE OR REPLACE TEMP VIEW`→`CREATE VIEW`, `date_trunc('MONTH', x)`→`TIMESTAMP_TRUNC(x, MONTH)`.
Original file line number	Diff line number	Diff line change
`@@ -16,4 +16,3 @@ SELECT row_id, TRANSFORM(nums, x -> x + 1) AS nums_plus_one`
`16`	`16`	`FROM collection_events;`
`17`	`17`	```
`18`	`18`
`19`		-Rewrites: `ARRAY<INT>`→`INT ARRAY`. Lambda syntax preserved.
Original file line number	Diff line number	Diff line change
`@@ -32,4 +32,3 @@ SELECT user_id, CARDINALITY(tags) AS tag_count,`
`32`	`32`	`FROM session_profiles;`
`33`	`33`	```
`34`	`34`
`35`		-Rewrites: `ARRAY<STRING>`→`VARCHAR ARRAY`, `size()`→`CARDINALITY()`, `element_at(map, key)`→`map[key]`.
Original file line number	Diff line number	Diff line change
`@@ -14,4 +14,3 @@ Feldera:`
`14`	`14`	`MAX(TIMESTAMPDIFF(DAY, shipped_at, delivered_at)) AS max_days`
`15`	`15`	```
`16`	`16`
`17`		-Rewrites: `datediff(end, start)`→`TIMESTAMPDIFF(DAY, start, end)` (argument order reversed).
Original file line number	Diff line number	Diff line change
`@@ -20,4 +20,3 @@ INNER JOIN order_facts o ON c.customer_id = o.customer_id`
`20`	`20`	`WHERE o.amount >= 500;`
`21`	`21`	```
`22`	`22`
`23`		-Rewrites: `LEFT SEMI JOIN`→`INNER JOIN` + `SELECT DISTINCT`.
Original file line number	Diff line number	Diff line change
`@@ -16,4 +16,3 @@ SELECT row_id, grp, COALESCE(amount, discount) AS resolved_amount`
`16`	`16`	`FROM scalar_function_rows;`
`17`	`17`	```
`18`	`18`
`19`		-Rewrites: `nvl(a, b)`→`COALESCE(a, b)`. Same for `ifnull(a, b)`.
Original file line number	Diff line number	Diff line change
`@@ -21,4 +21,3 @@ SELECT category, product_id, revenue FROM (`
`21`	`21`	`) ranked WHERE rn <= 3;`
`22`	`22`	```
`23`	`23`
`24`		-ROW_NUMBER with TopK filter (`WHERE rn <= N`) passes through directly.