Scalar Functions — JSON / Dynamic

KQL treats JSON objects and arrays as first-class dynamic values. These functions let you extract fields, pack values into objects or arrays, and serialize dynamic data back to JSON strings. Dotted property access (obj.field) is supported across all dialects and is rewritten as a json_extract_string call automatically.

Legend: ✅ Supported · ⚠️ Approximated · ❌ Not Supported · 🔄 Rewritten

Function	SQLite	MySQL	ClickHouse	PostgreSQL	DuckDB	Notes
`json_extract_string()`	✅	✅	✅	✅	✅	`json_extract` / `JSONExtractString` / `->>` / `json_extract_string`
`json_extract_raw()`	✅	✅	✅	✅	✅	`json_extract` / `JSONExtractRaw` / `->`
`dynamic_to_json()`	❌	❌	✅	✅	✅	ClickHouse: `toJSONString`; Postgres: CAST to text; DuckDB: CAST to `VARCHAR`
`parse_json()`	❌	✅	✅	✅	✅	Parses a string to a dynamic value: DuckDB/MySQL CAST to `JSON`, Postgres CAST to `jsonb`
`todynamic()` / `toobject()`	❌	✅	✅	✅	✅	ClickHouse: `JSONExtractRaw`; Postgres: CAST to `jsonb`; DuckDB: CAST to `JSON`; MySQL: `JSON_EXTRACT($)`
`extract_json()` / `extractjson()`	✅	✅	✅	⚠️	✅	JSONPath extraction with optional type cast; DuckDB: `json_extract_string`; Postgres: simple paths only (`#>>`)
`bag_pack()` / `pack()`	✅	✅	✅	✅	✅	`json_object` / `jsonb_build_object` / `map()`; DuckDB: `json_object`
`pack_all()`	❌	❌	❌	❌	❌	Dynamic column enumeration has no SQL equivalent
`pack_array()`	❌	❌	✅	✅	✅	ClickHouse: `array()`; Postgres: `ARRAY()`; DuckDB: `list_value()`
`bag_keys()`	❌	❌	✅	❌	✅	ClickHouse: `JSONExtractKeys()`; DuckDB: `json_keys()`
`bag_merge()`	❌	❌	❌	❌	✅	DuckDB: `json_merge_patch()`; ClickHouse rejects (no lossless JSON merge)
Dotted property access `obj.field`	✅	✅	✅	✅	✅	Mapped to each dialect's JSON extraction (SQLite/MySQL use a `$.`-prefixed path; only the final hop extracts text). Variable-key access `d[k]` is unsupported on SQLite
Array index access `arr[N]`	❌	❌	✅	✅	❌	Element access by ordinal; ClickHouse and Postgres only