> ## Documentation Index
> Fetch the complete documentation index at: https://axiom-mano-sample-app.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL functions

> Learn how to use SQL functions in APL

## SQL functions

| **Function Name**            | **Description**                                                                                                    |
| ---------------------------- | ------------------------------------------------------------------------------------------------------------------ |
| [parse\_sql()](#parse-sql)   | Interprets and analyzes SQL queries, making it easier to extract and understand SQL statements within datasets.    |
| [format\_sql()](#format-sql) | Converts the data model produced by `parse_sql()` back into a SQL statement for validation or formatting purposes. |

## parse\_sql()

Analyzes an SQL statement and constructs a data model, enabling insights into the SQL content within a dataset.

### Limitations

* It is mainly used for simple SQL queries. SQL statements like stored procedures, Windows functions, common table expressions (CTEs), recursive queries, advanced statistical functions, and special joins are not supported.

### Arguments

| **Name**       | **Type** | **Required or Optional** | **Description**               |
| -------------- | -------- | ------------------------ | ----------------------------- |
| sql\_statement | string   | Required                 | The SQL statement to analyze. |

### Returns

A dictionary representing the structured data model of the provided SQL statement. This model includes maps or slices that detail the various components of the SQL statement, such as tables, fields, conditions, etc.

### Examples

### Basic data retrieval

The SQL statement **`SELECT * FROM db`** retrieves all columns and rows from the table named **`db`**.

```kusto
hn
| project parse_sql("select * from db")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20project%20parse_sql\('select%20*%20from%20db'\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### WHERE Clause

This example parses a **`SELECT`** statement with a **`WHERE`** clause, filtering **`customers`** by **`subscription_status`**.

```kusto
hn
| project parse_sql("SELECT id, email FROM customers WHERE subscription_status = 'active'")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20project%20parse_sql\(%5C%22SELECT%20id%2C%20email%20FROM%20customers%20WHERE%20subscription_status%20%3D%20'active'%5C%22\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### JOIN operation

This example shows parsing an SQL statement that performs a **`JOIN`** operation between **`orders`** and **`customers`** tables to match orders with customer names.

```kusto
hn
| project parse_sql("SELECT orders.id, customers.name FROM orders JOIN customers ON orders.customer_id = customers.id")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20project%20parse_sql\(%5C%22SELECT%20orders.id%2C%20customers.name%20FROM%20orders%20JOIN%20customers%20ON%20orders.customer_id%20%3D%20customers.id%5C%22\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### GROUP BY Clause

In this example, the **`parse_sql()`** function is used to parse an SQL statement that aggregates order counts by **`product_id`** using the **`GROUP BY`** clause.

```kusto
hn
| project parse_sql("SELECT product_id, COUNT(*) as order_count FROM orders GROUP BY product_id")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20project%20parse_sql\(%5C%22SELECT%20product_id%2C%20COUNT\(*\)%20as%20order_count%20FROM%20orders%20GROUP%20BY%20product_id%5C%22\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### Nested Queries

This example demonstrates parsing a nested SQL query, where the inner query selects **`user_id`** from **`orders`** based on **`purchase_date`**, and the outer query selects names from **`users`** based on those IDs.

```kusto
hn
| project parse_sql("SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE purchase_date > '2022-01-01')")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20project%20parse_sql\(%5C%22SELECT%20name%20FROM%20users%20WHERE%20id%20IN%20\(SELECT%20user_id%20FROM%20orders%20WHERE%20purchase_date%20%3E%20'2022-01-01'\)%5C%22\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### ORDER BY Clause

Here, the example shows how to parse an SQL statement that orders **`users`** by **`registration_date`** in descending order.

```kusto
hn
| project parse_sql("SELECT name, registration_date FROM users ORDER BY registration_date DESC")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20project%20parse_sql\(%5C%22SELECT%20name%2C%20registration_date%20FROM%20users%20ORDER%20BY%20registration_date%20DESC%5C%22\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### Sorting users by registration data

This example demonstrates parsing an SQL statement that retrieves the **`name`** and **`registration_date`** of users from the **`users`** table, and orders the results by **`registration_date`** in descending order, showing how to sort data based on a specific column.

```kusto
hn | extend parse_sql("SELECT name, registration_date FROM users ORDER BY registration_date DESC")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20extend%20parse_sql\(%5C%22SELECT%20name%2C%20registration_date%20FROM%20users%20ORDER%20BY%20registration_date%20DESC%5C%22\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### Querying with index hints to use a specific index

This query hints at MySQL to use a specific index named **`index_name`** when executing the SELECT statement on the **`users`** table.

```kusto
hn 
| project parse_sql("SELECT * FROM users USE INDEX (index_name) WHERE user_id = 101")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20project%20parse_sql\(%5C%22SELECT%20*%20FROM%20users%20USE%20INDEX%20\(index_name\)%20WHERE%20user_id%20%3D%20101%5C%22\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### Inserting data with ON DUPLICATE KEY UPDATE

This example showcases MySQL’s ability to handle duplicate key entries elegantly by updating the existing record if the insert operation encounters a duplicate key.

```kusto
hn 
| project parse_sql("INSERT INTO settings (user_id, setting, value) VALUES (1, 'theme', 'dark') ON DUPLICATE KEY UPDATE value='dark'")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20project%20parse_sql\(%5C%22INSERT%20INTO%20settings%20\(user_id%2C%20setting%2C%20value\)%20VALUES%20\(1%2C%20'theme'%2C%20'dark'\)%20ON%20DUPLICATE%20KEY%20UPDATE%20value%3D'dark'%5C%22\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### Using JSON functions

This query demonstrates MySQL’s support for JSON data types and functions, extracting the age from a JSON object stored in the **`user_info`** column.

```kusto
hn 
| project parse_sql("SELECT JSON_EXTRACT(user_info, '$.age') AS age FROM users WHERE user_id = 101")
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20project%20parse_sql\(%5C%22SELECT%20JSON_EXTRACT\(user_info%2C%20%27%24.age%27\)%20AS%20age%20FROM%20users%20WHERE%20user_id%20%3D%20101%5C%22\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

## format\_sql()

Transforms the data model output by `parse_sql()` back into a SQL statement. Useful for testing and ensuring that the parsing accurately retains the original structure and intent of the SQL statement.

### Arguments

| **Name**           | **Type**   | **Required or Optional** | **Description**                                    |
| ------------------ | ---------- | ------------------------ | -------------------------------------------------- |
| parsed\_sql\_model | dictionary | Required                 | The structured data model output by `parse_sql()`. |

### Returns

A string that represents the SQL statement reconstructed from the provided data model.

### Examples

### Reformatting a basic SELECT Query

After parsing a SQL statement, you can reformat it back to its original or a standard SQL format.

```kusto
hn
| extend parsed = parse_sql("SELECT * FROM db")
| project formatted_sql = format_sql(parsed)
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20extend%20parsed%20%3D%20parse_sql\(%5C%22SELECT%20*%20FROM%20db%5C%22\)%20%7C%20project%20formatted_sql%20%3D%20format_sql\(parsed\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### Formatting SQL Queries

This example first parses a SQL statement to analyze its structure and then formats the parsed structure back into a SQL string using `format_sql`.

```kusto
hn 
| extend parsed = parse_sql("SELECT name, registration_date FROM users ORDER BY registration_date DESC")
| project format_sql(parsed)
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20extend%20parsed%20%3D%20parse_sql\(%5C%22SELECT%20name%2C%20registration_date%20FROM%20users%20ORDER%20BY%20registration_date%20DESC%5C%22\)%20%7C%20project%20formatted_sql%20%3D%20format_sql\(parsed\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### Formatting a simple SELECT Statement

This example demonstrates parsing a straightforward `SELECT` statement that retrieves user IDs and usernames from an `user_accounts` table where the `active` status is `1`. After parsing, it uses `format_sql` to convert the parsed data back into a SQL string.

```kusto
hn 
| extend parsed = parse_sql("SELECT user_id, username FROM user_accounts WHERE active = 1")
| project formatted_sql = format_sql(parsed)
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20extend%20parsed%20%3D%20parse_sql\(%5C%22SELECT%20user_id%2C%20username%20FROM%20user_accounts%20WHERE%20active%20%3D%201%5C%22\)%20%7C%20project%20formatted_sql%20%3D%20format_sql\(parsed\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### Reformatting a complex query with JOINS

In this example, a more complex SQL statement involving an `INNER JOIN` between `orders` and `customers` tables is parsed. The query selects orders and customer names for orders placed after January 1, 2023. `format_sql` is then used to reformat the parsed structure into a SQL string.

```kusto
hn 
| extend parsed = parse_sql("SELECT orders.order_id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id WHERE orders.order_date > '2023-01-01'")
| project formatted_sql = format_sql(parsed)
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20extend%20parsed%20%3D%20parse_sql\(%5C%22SELECT%20orders.order_id%2C%20customers.name%20FROM%20orders%20INNER%20JOIN%20customers%20ON%20orders.customer_id%20%3D%20customers.id%20WHERE%20orders.order_date%20%3E%20'2023-01-01'%5C%22\)%20%7C%20project%20formatted_sql%20%3D%20format_sql\(parsed\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)

### Using format\_sql with aggregation functions

This example focuses on parsing an SQL statement that performs aggregation. It selects product IDs and counts of total sales from a `sales` table, grouping by `product_id` and having a condition on the count. After parsing, `format_sql` reformats the output into an SQL string.

```kusto
hn 
| extend parsed = parse_sql("SELECT product_id, COUNT(*) as total_sales FROM sales GROUP BY product_id HAVING COUNT(*) > 100")
| project formatted_sql = format_sql(parsed)
```

[Run in Playground](https://play.axiom.co/axiom-play-qf1k/query?initForm=%7B%22apl%22%3A%22hn%20%7C%20extend%20parsed%20%3D%20parse_sql\(%5C%22SELECT%20product_id%2C%20COUNT\(*\)%20as%20total_sales%20FROM%20sales%20GROUP%20BY%20product_id%20HAVING%20COUNT\(*\)%20%3E%20100%5C%22\)%20%7C%20project%20formatted_sql%20%3D%20format_sql\(parsed\)%22%2C%22queryOptions%22%3A%7B%22quickRange%22%3A%2290d%22%7D%7D)