# Special Functions

- [Dynamic Columns Functions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/dynamic-columns-functions.md): Manage schema-less data within relational tables. These functions, such as COLUMN\_CREATE and COLUMN\_GET, allow you to store and retrieve variable sets of columns in a single BLOB field.
- [COLUMN\_ADD](https://mariadb.com/docs/server/reference/sql-functions/special-functions/dynamic-columns-functions/column_add.md): Update dynamic columns. This function adds or updates values within a dynamic column blob, returning the new blob content.
- [COLUMN\_CHECK](https://mariadb.com/docs/server/reference/sql-functions/special-functions/dynamic-columns-functions/column_check.md): Verify dynamic column integrity. This function checks if a blob containing dynamic columns is valid and returns 1 if it is, 0 otherwise.
- [COLUMN\_CREATE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/dynamic-columns-functions/column_create.md): Create a dynamic column blob. This function generates a binary string containing specified column names and values for storage in a BLOB.
- [COLUMN\_DELETE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/dynamic-columns-functions/column_delete.md): Remove dynamic columns. This function deletes specified columns from a dynamic column blob and returns the updated blob.
- [COLUMN\_EXISTS](https://mariadb.com/docs/server/reference/sql-functions/special-functions/dynamic-columns-functions/column_exists.md): Check for a dynamic column. This function returns 1 if a specified column exists within a dynamic column blob, and 0 otherwise.
- [COLUMN\_GET](https://mariadb.com/docs/server/reference/sql-functions/special-functions/dynamic-columns-functions/column_get.md): Retrieve a dynamic column value. This function extracts a specific column's value from a dynamic column blob, casting it to a specified type.
- [COLUMN\_JSON](https://mariadb.com/docs/server/reference/sql-functions/special-functions/dynamic-columns-functions/column_json.md): Convert dynamic columns to JSON. This function returns a JSON string representation of the data stored in a dynamic column blob.
- [COLUMN\_LIST](https://mariadb.com/docs/server/reference/sql-functions/special-functions/dynamic-columns-functions/column_list.md): List dynamic column names. This function returns a comma-separated list of all column names contained within a dynamic column blob.
- [Galera Functions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/galera-functions.md): Interact with the Galera Cluster plugin. These functions provide internal status information and control mechanisms specific to synchronous multi-master replication nodes.
- [WSREP\_LAST\_SEEN\_GTID](https://mariadb.com/docs/server/reference/sql-functions/special-functions/galera-functions/wsrep_last_seen_gtid.md): Return the GTID of the most recent write transaction. This function helps determine the transaction ID to use for synchronization waiting.
- [WSREP\_LAST\_WRITTEN\_GTID](https://mariadb.com/docs/server/reference/sql-functions/special-functions/galera-functions/wsrep_last_written_gtid.md): Return the GTID of the client's last write. This function identifies the specific transaction ID generated by the current client's most recent write operation.
- [WSREP\_SYNC\_WAIT\_UPTO\_GTID](https://mariadb.com/docs/server/reference/sql-functions/special-functions/galera-functions/wsrep_sync_wait_upto_gtid.md): Wait for a specific GTID to be applied. This function blocks the client until the node has committed the transaction with the specified Global Transaction ID.
- [Geographic Functions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/geographic-functions.md): Process geospatial data. This collection of functions allows you to create, analyze, and manipulate geometric shapes like points, lines, and polygons within your database.
- [JSON Functions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions.md): Complete JSON Functions reference: JSON\_EXTRACT(), JSON\_SET(), JSON\_REPLACE(), JSON\_SEARCH() syntax for path queries, document updates, and value retrieval.
- [JSONPath Expressions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/jsonpath-expressions.md): Understand JSONPath syntax. This guide explains how to use JSONPath to select and extract specific elements, objects, or arrays from JSON documents.
- [Differences between JSON\_QUERY and JSON\_VALUE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/differences-between-json_query-and-json_value.md): Learn the difference between JSON\_QUERY and JSON\_VALUE in MariaDB. JSON\_QUERY returns objects or arrays, while JSON\_VALUE extracts scalar values.
- [JSON\_ARRAY](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_array.md): Create a JSON array. This function evaluates a list of values and returns a JSON array containing those values.
- [JSON\_ARRAY\_APPEND](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_array_append.md): Explore JSON\_ARRAY\_APPEND in MariaDB. This function appends values to the end of specified arrays within a JSON document, returning the modified result.
- [JSON\_ARRAY\_INSERT](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_array_insert.md): This function inserts values into a JSON document at a specified path, returning the modified document, and supports evaluating multiple path-value pairs sequentially.
- [JSON\_ARRAY\_INTERSECT](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_array_intersect.md): Available from version 11.2, this function compares two JSON arrays and returns a new array containing only the items present in both.
- [JSON\_COMPACT](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_compact.md): Learn about the JSON\_COMPACT function in MariaDB. This function removes all unnecessary whitespace from a JSON document, minimizing its size for efficient storage and transmission.
- [JSON\_CONTAINS](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_contains.md): Check for JSON containment. This function returns 1 if a candidate JSON document is contained within a target JSON document, or 0 otherwise.
- [JSON\_CONTAINS\_PATH](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_contains_path.md): Check for a JSON path. This function returns 1 if a specified path exists within a JSON document, or 0 if it does not.
- [JSON\_DEPTH](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_depth.md): Discover JSON\_DEPTH in MariaDB. This function returns the maximum depth of a JSON document, assigning a depth of 1 to scalars and empty structures, and higher values for nested data.
- [JSON\_DETAILED](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_detailed.md): Explore JSON\_DETAILED in MariaDB. Also known as JSON\_PRETTY, this function formats JSON documents with indentation and newlines to emphasize nested structures for readability.
- [JSON\_EQUALS](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_equals.md): Discover JSON\_EQUALS in MariaDB. This function checks for equality between two JSON objects, returning 1 if they are equal, 0 if not, handling key order and data types intelligently.
- [JSON\_EXISTS](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_exists.md): Explore JSON\_EXISTS in MariaDB. This function checks whether a specified JSON document contains an element at a given path, returning 1 for existence and 0 otherwise.
- [JSON\_EXTRACT](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_extract.md): Complete JSON\_EXTRACT() reference: JSON\_EXTRACT(json\_doc, path\[, path]...) syntax, JSONPath arguments, NULL/error behavior, and multi-match array autowrap.
- [JSON\_INSERT](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_insert.md): Learn about the JSON\_INSERT function in MariaDB. This function inserts new data into a JSON document without replacing existing values, returning the updated document or NULL.
- [JSON\_KEY\_VALUE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_key_value.md): Explore JSON\_KEY\_VALUE in MariaDB. Available from version 11.2, this function extracts key/value pairs from a JSON object, enabling easier data transformation and usage with JSON\_TABLE.
- [JSON\_KEYS](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_keys.md): Return keys from a JSON object. This function returns the keys from the top-level value of a JSON object as a JSON array.
- [JSON\_LENGTH](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_length.md): Return the length of a JSON document. This function returns the length of a JSON document or, if a path is given, the length of the value within the path.
- [JSON\_LOOSE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_loose.md): Discover JSON\_LOOSE in MariaDB. This function adds spaces to a JSON document to improve its readability, providing a format that is easier for humans to scan than compact JSON.
- [JSON\_MERGE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_merge.md): Merge JSON documents. This function merges two or more JSON documents into a single JSON document, preserving all keys and values.
- [JSON\_MERGE\_PATCH](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_merge_patch.md): Learn about JSON\_MERGE\_PATCH in MariaDB. This RFC 7396-compliant function merges JSON documents by overwriting duplicate keys, serving as a modern replacement for the deprecated JSON\_MERGE.
- [JSON\_MERGE\_PRESERVE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_merge_preserve.md): Explore JSON\_MERGE\_PRESERVE in MariaDB. This function merges JSON documents while keeping all members, including those with duplicate keys, effectively acting as a synonym for the legacy JSON\_MERGE.
- [JSON\_NORMALIZE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_normalize.md): Discover JSON\_NORMALIZE in MariaDB. This function recursively sorts keys and removes spaces from a JSON document, enabling reliable equality comparisons and unique constraints on JSON data.
- [JSON\_OBJECT](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_object.md): Create a JSON object. This function evaluates a list of key-value pairs and returns a JSON object containing those pairs.
- [JSON\_OBJECT\_FILTER\_KEYS](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_object_filter_keys.md): Discover JSON\_OBJECT\_FILTER\_KEYS in MariaDB. Available from version 11.2, this function returns a new JSON object containing only the key-value pairs where the keys match those provided in a specified
- [JSON\_OBJECT\_TO\_ARRAY](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_object_to_array.md): Explore JSON\_OBJECT\_TO\_ARRAY in MariaDB. Available from version 11.2, this function converts JSON objects into arrays of key-value pairs, facilitating operations like finding common values when used w
- [JSON\_OVERLAPS](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_overlaps.md): Discover JSON\_OVERLAPS in MariaDB. Available from version 10.9, this function compares two JSON documents, returning true if they share at least one common key-value pair, array element, or scalar val
- [JSON\_PRETTY](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_pretty.md): Learn about JSON\_PRETTY in MariaDB. This function is an alias for JSON\_DETAILED, serving to format JSON documents in a human-readable way by adding indentation and newlines.
- [JSON\_QUERY](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_query.md): Extract a JSON object or array. This function extracts data from a JSON document at a given path, returning a JSON object or array.
- [JSON\_QUOTE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_quote.md): Quote a string as a JSON value. This function wraps a string with double quotes and escapes special characters to create a valid JSON string literal.
- [JSON\_REMOVE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_remove.md): Remove data from a JSON document. This function removes data from a JSON document at a specified path and returns the result.
- [JSON\_REPLACE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_replace.md): Replace values in a JSON document. This function replaces existing values in a JSON document and returns the result.
- [JSON\_SCHEMA\_VALID](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_schema_valid.md): Discover JSON\_SCHEMA\_VALID in MariaDB. Available from version 11.1, this function validates a JSON document against a specified JSON Schema (Draft 2020), returning true if valid.
- [JSON\_SEARCH](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_search.md): Search for a value in a JSON document. This function returns the path to the given string within a JSON document.
- [JSON\_SET](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_set.md): Insert or update data in a JSON document. This function inserts or updates data in a JSON document at a specified path and returns the result.
- [JSON\_TABLE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_table.md): Convert JSON data to a relational table. This table function extracts data from a JSON document and returns it as a relational table with columns.
- [JSON\_TYPE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_type.md): Return the type of a JSON value. This function returns a string indicating the type of a JSON value (e.g., OBJECT, ARRAY, INTEGER).
- [JSON\_UNQUOTE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_unquote.md): Unquote a JSON value. This function removes the quotes from a JSON string and unescapes special characters.
- [JSON\_VALID](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_valid.md): Check if a value is valid JSON. This function returns 1 if the argument is a valid JSON document, and 0 otherwise.
- [JSON\_VALUE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_value.md): Extract a scalar value from a JSON document. This function extracts a value from a JSON document at a given path and returns it as a scalar.
- [JSON Validation Function](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json-validation-function.md)
- [Window Functions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions.md): Explore window functions in MariaDB Server. This section details SQL functions that perform calculations across a set of table rows related to the current row, enabling advanced analytical queries.
- [Window Functions Overview](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/window-functions-overview.md): Understand window function concepts. These functions operate on a set of rows (window) defined by an OVER clause, returning a value for each row without collapsing results.
- [Window Frames](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/window-frames.md): Define subsets of rows for calculation. Window frames specify which rows relative to the current row are included in the window function's calculation.
- [ColumnStore Window Functions](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/window-functions-columnstore-window-functions.md): Review window function support in ColumnStore. This page details the specific window functions and limitations applicable when using the ColumnStore storage engine.
- [CUME\_DIST](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/cume_dist.md): Calculate cumulative distribution. This function returns the relative rank of the current row within its partition, calculated as the percentage of rows preceding or peer to it.
- [DENSE\_RANK](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/dense_rank.md): Calculate rank without gaps. This function assigns a rank to each row within a partition, with tied values receiving the same rank and no numbers skipped.
- [FIRST\_VALUE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/first_value.md): Return the value from the first row in the window frame. This function retrieves the value of an expression from the first row of the defined window.
- [LAG](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/lag.md): Access data from a preceding row. This function returns the value of an expression from a row at a specified physical offset prior to the current row.
- [LEAD](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/lead.md): Access data from a following row. This function returns the value of an expression from a row at a specified physical offset after the current row.
- [MEDIAN](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/median.md): Calculate the median value. This window function returns the middle value (50th percentile) of an ordered set of values within the window.
- [NTH\_VALUE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/nth_value.md): Return the value from the N-th row. This function retrieves the value of an expression from the N-th row within the window frame.
- [NTILE](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/ntile.md): Distribute rows into buckets. This function divides the rows in an ordered partition into a specified number of approximately equal groups.
- [PERCENT\_RANK](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/percent_rank.md): Calculate relative rank. This function returns the percentage rank of the current row within its partition, ranging from 0 to 1.
- [PERCENTILE\_CONT](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/percentile_cont.md): Calculate a continuous percentile. This inverse distribution function returns an interpolated value that corresponds to the specified percentile.
- [PERCENTILE\_DISC](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/percentile_disc.md): Calculate a discrete percentile. This inverse distribution function returns an actual value from the dataset that corresponds to the specified percentile.
- [RANK](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/rank.md): Calculate rank with gaps. This function assigns a rank to each row, with tied values receiving the same rank and subsequent ranks skipped.
- [ROW\_NUMBER](https://mariadb.com/docs/server/reference/sql-functions/special-functions/window-functions/row_number.md): Complete ROW\_NUMBER() reference: ROW\_NUMBER() OVER() window syntax, PARTITION BY clauses, ORDER BY ordering, and RANK()/DENSE\_RANK() comparisons.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-functions/special-functions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.