---
schema_version: "1"
id: 503a6fee85fa7dbafbf35a6f43e7178f00ad5a3149804a0a2dede2e45e3d6e65
title: Data Serialization Functions for SQL and SOQL
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/analytics/bi_dev_guide_bindings/guides/bi-dashboard-bindings-functions-sql-soql-data-serialization.md
canonical_url: https://developer.salesforce.com/docs/analytics/bi-dev-guide-bindings/guide/bi-dashboard-bindings-functions-sql-soql-data-serialization.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 8f6da02728b694dd39bab23862e752eb5062aa2d0ce58a3fea8dff14ee27e22d
products:
  - Analytics Interactions
  - analytics
---
<!-- Contains overview of serialization functions, asSQLGrouping Function, asSQLHaving Function, asSQLOrder Function, asSQLSelect Function, and asSQLWhere Function -->
# Data Serialization Functions for SQL and SOQL

SQL and SOQL serialization functions convert the data into the form expected by the query in which the interaction is inserted.

## asSQLGrouping Function

Returns a grouping as a string in a SQL or SOQL query. Can also return multiple groupings.

The input data must be a scalar or a one-dimensional array of groupings. Null or no results returns an ungrouped query.

### Syntax

```javascript
<input data>.asSQLGrouping(SQLType, includeKeywords)
```

### Arguments

| Argument  | Description                            |
| --------- | -------------------------------------- |
| `SQLType`   | (Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`. |
| `includeKeywords` | (Optional) The default value is `false`. |


**note:** For SOQL, use `sobject`.


Let's look at some examples where the selection detemines the groupings in a SQL or SOQL-form query. These examples are based on the `stepFoo` source query. Assume that `stepFoo.selection` retrieves these rows from the query.

```json
[
    {grouping: "first", alias: "foo"}
    {grouping: "second", alias: "bar"}
]
```

### Examples
Function:
```javascript
(cell(stepFoo.selection, 0, "grouping").asSQLGrouping("snowflake", true))
```

Output in standard or Snowflake live dataset:

```sql
GROUP BY "first"
```

Function:
```javascript
(cell(stepFoo.selection, 0, "grouping").asSQLGrouping("sboject", true))
```

Output in SOQL:

```sql
GROUP BY first
```
---
Function:
```javascript
(cell(stepFoo.selection, 0, "grouping").asSQLGrouping("snowflake", false))
```

Output in standard or Snowflake live dataset:

```sql
"first"
```

Function:
```javascript
(cell(stepFoo.selection, 0, "grouping").asSQLGrouping("sboject", false))
```

Output in SOQL:

```sql
first
```
---

To make the interaction return multiple fields for the grouping, replace the `cell` interaction function with a `column` function and update the arguments.

Function:
```javascript
(column(stepFoo.selection, ["alias"]).asSQLGrouping("snowflake", true))
```

Output in standard or Snowflake live dataset:

```sql
GROUP BY "foo", "bar"
```

Function:
```javascript
(column(stepFoo.selection, ["alias"]).asSQLGrouping("sobject", true))
```

Output in SOQL:

```sql
GROUP BY foo, bar
```
---
Function:
```javascript
(column(stepFoo.selection, ["alias"]).asSQLGrouping("snowflake", false))
```

Output in standard or Snowflake live dataset:

```sql
"foo", "bar"
```

Function:
```javascript
(column(stepFoo.selection, ["alias"]).asSQLGrouping("sobject", false))
```

Output in SOQL:

```sql
foo, bar
```

## asSQLHaving Function

Returns the grouped results for a SQL or SOQL query restricted by a conditional filter logic applied on aggregate functions. This function is similar to the `asSQLWHERE` function.

### Syntax

```javascript
<input data>.asSQLHaving(SQLType, includeKeywords)
```

### Arguments

| Argument  | Description                            |
| --------- | -------------------------------------- |
| `SQLType`   | (Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`. |
| `includeKeywords` | (Optional) The default value is `false`. |


**note:** For SOQL, use `sobject`.


These examples are based on the `stepFoo` source query.


**note:** The  `<` and `>=<=` operators aren't supported.


### Examples
Function:
```javascript
(toArray("SUM(Revenue)", ">",column(stepFoo.selection, ["Revenue"]).asSQLHaving("snowflake", true))
```

Output in standard or Snowflake live dataset:

```sql
HAVING "SUM(Revenue)" > 10
```

Function:
```javascript
(toArray("SUM(Revenue)", ">",column(stepFoo.selection, ["Revenue"]).asSQLHaving("sobject", true))
```

Output in SOQL:

```sql
HAVING SUM(Revenue) > 10
```

---
Function:
```javascript
(toArray("SUM(Revenue)", ">",column(stepFoo.selection, ["Revenue"]).asSQLHaving("dataset"))
```

Output in standard or Snowflake live dataset:

```sql
"SUM(Revenue)" > 10
```

Output in SOQL:

```sql
SUM(Revenue) > 10
```
---

Function:
```javascript
([].asSQLHaving('dataset')) or ([].asSQLHaving('dataset', 'false'))
```

Output in standard, Snowflake live dataset, or SOQL:

```sql
""
```

## asSQLOrder Function

Returns the sort order as a string for a SQL or SOQL query.

The input data must be scalar, one-dimensional, or a two-dimensional array. A two-dimensional array is treated as a tuple of interactions.

### Syntax

```javascript
<input data>.asSQLOrder(SQLType, includeKeywords)
```

### Arguments

| Argument  | Description                            |
| --------- | -------------------------------------- |
| `SQLType`   | (Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`. |
| `includeKeywords` | (Optional) The default value is `false`. |


**note:** For SOQL, use `sobject`.


These examples are based on the `stepFoo` source query. Assume that `stepFoo.selection` retrieves these rows from the query.

```json
values: [
    {order: "first", direction: "desc"}
    {order: "second", direction: "asc"}
]
```

### Examples
Function:
```javascript
(cell(stepFoo.selection, 0, "order").asSQLOrder("snowflake", true))
```

Output in standard or Snowflake live dataset:

```sql
ORDER BY "first"
```

Function:
```javascript
(cell(stepFoo.selection, 0, "order").asSQLOrder("sboject", true))
```

Output in SOQL:

```sql
ORDER BY first
```
---
Function:
```javascript
(cell(stepFoo.selection,["order")].asSQLOrder("snowflake"))
```

Output in standard or Snowflake live dataset:

```sql
"first", "second"
```

Function:
```javascript
(cell(stepFoo.selection, ["order"]).asSQLOrder("sboject"))
```

Output in SOQL:

```sql
first, second
```
---
Function:
```javascript
(row(stepFoo.selection, [], ["order", "direction"]).asSQLOrder("snowflake", true))
```

Output in standard or Snowflake live dataset:

```sql
ORDER BY "first" DESC, "second", ASC
```

Function:
```javascript
(row(stepFoo.selection, [], ["order", "direction"]).asSQLOrder("sobject", true))
```

Output in SOQL:

```sql
ORDER BY first DESC, second ASC
```

## asSQLSelect Function

Returns the SQL or SOQL expression and alias as a string that you can use to project a field in the query. The query expression determines the value of the field. The alias is the field label.


### Syntax

```javascript
<input data>.asSQLSelect(SQLType)
```

### Arguments

| Argument  | Description                            |
| --------- | -------------------------------------- |
| `SQLType`   | (Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`. |


**note:** For SOQL, use `sobject`.


These examples are based on the `stepFoo` source query. Assume that `stepFoo.selection` retrieves these rows from the query.

```json
values: [
    {expression: "SUM("first")", alias: "foo"}
    {expression: "second", alias: "bar"}
]
```

### Examples
Function:
```javascript
(row(stepFoo.selection, [0], ["expression", "alias"]).asSQLSelect("snowflake"))
```

Output in standard or Snowflake live dataset:

```sql
SUM("first") AS "foo"
```

Function:
```javascript
(row(stepFoo.selection, [0], ["expression", "alias"]).asSQLSelect("sobject"))
```

Output in SOQL:

```sql
SUM("first") foo
```
---
Function:
```javascript
(row(stepFoo.selection, [0], ["expression"]).asSQLSelect("snowflake"))
```

Output in standard or Snowflake live dataset:

```sql
SUM("first")
```

Function:
```javascript
(row(stepFoo.selection, [0], ["expression"]).asSQLSelect("sobject"))
```

Output in SOQL:

```sql
SUM("first")
```
---

Function:
```javascript
(row(stepFoo.selection, [], ["expression", "alias"]).asSQLSelect("snowflake"))
```

Output in standard or Snowflake live dataset:

```sql
SUM("first") AS "foo", "second" AS "bar"
```

Function:
```javascript
(row(stepFoo.selection, [], ["expression", "alias"]).asSQLSelect("sobject"))
```

Output in SOQL:

```sql
SUM("first") foo, second bar
```

## asSQLWhere Function

Returns the SQL or SOQL query results restricted by a conditional filter logic applied on a measure, dimension, or date.

### Syntax

```javascript
<input data>.asSQLWhere(SQLType, includeKeywords)
```

### Arguments

| Argument  | Description                            |
| --------- | -------------------------------------- |
| `SQLType`   | (Required) Specify SQL or SOQL variant. Valid values are `dataset`, `snowflake`, and `sobject`. |
| `includeKeywords` | (Optional) The default is false. |


**note:** For SOQL, use `sobject`.


These examples are based on the `stepFoo` source query.

### Examples
Function:
```javascript
([].asSQLWhere('dataset')) or ([].asSQLWhere('dataset', 'false'))
```

Output in standard dataset, Snowflake live dataset, or SOQL:

```sql
""
```
---
Function:
```javascript
((toArray("foo", "IN", column(stepFoo.selection, ["foo"])).asSQLWhere("snowflake", true))
toArray("foo", "IN", column(stepFoo.selection, ["foo"])  =>  ["foo", "IN", ["a", "b"]]
```

Output in standard or Snowflake live dataset:
```sql
WHERE "foo" in ('a', 'b')
```
Function:
```javascript
((toArray("foo", "IN", column(stepFoo.selection, ["foo"])).asSQLWhere("sobject", true))
toArray("foo", "IN", column(stepFoo.selection, ["foo"])  =>  ["foo", "IN", ["a", "b"]]
```
Output in SOQL:
```sql
WHERE foo in ('a', 'b')
```
---
Function:
```javascript
((toArray("bar", "><", column(stepFoo.selection, ["bar"])).asSQLWhere("snowflake", true))
toArray("bar", "><", column(stepFoo.selection, ["bar"])  =>  ["bar", "><", [10, 100]]
```

Output in standard or Snowflake live dataset:
```sql
"bar" > 10 AND "bar" < 100
```
Function:
```javascript
((toArray("bar", "><", column(stepFoo.selection, ["bar"])).asSQLWhere("sobject", true))
toArray("bar", "><", column(stepFoo.selection, ["bar"])  =>  ["bar", "><", [10, 100]]
```
Output in SOQL:
```sql
bar > 10 ad bar < 100
```
---
*Multiple Filters*

Function:
```javascript
(toArray(toArray("foo", "IN", column(stepFoo.selection, ["foo"]),
toArray("bar", "><;", column(stepFoo.selection, ["bar"]))).asSQLWhere("snowflake", true))
```

Output in standard or Snowflake live dataset:
```sql
WHERE "foo" IN ('a', 'b') AND ("bar" > 10 AND "bar" < 100)
```
Function:
```javascript
(toArray(toArray("foo", "IN", column(stepFoo.selection, ["foo"]),
toArray("bar", "><;", column(stepFoo.selection, ["bar"]))).asSQLWhere("sobject", true))
```
Output in SOQL:
```sql
WHERE foo IN ('a', 'b') AND (bar > 10 AND bar < 100)
```
---
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("dataset", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [1016504910000, 1281655993000]]
```

Output in standard dataset:
```sql
WHERE ("CreatedDate"  >= DATE '2002-3-19' AND "CreatedDate" &lt;= '2010-8-12'))
```
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("snowflake", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [1016504910000, 1281655993000]]
```

Output in Snowflake live dataset:
```sql
WHERE ("CreatedDate"  BETWEEN to_date('2002-3-19') AND to_date('2010-8-12'))
```
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("sobject", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [1016504910000, 1281655993000]]
```
Output in SOQL:
```sql
WHERE (CreatedDate  >= DATE 2002-3-19 AND CreatedDate <= 2010-8-12))
```
---
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("dataset"))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [null, 1281655993000]]
```

Output in standard dataset:
```sql
"CreatedDate" <= DATE '2010-8-12'
```
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("snowflake"))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [null, 1281655993000]]
```

Output in Snowflake live dataset:
```sql
"CreatedDate" <= to_date('2010-8-12')
```
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("sobject"))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [null, 1281655993000]]
```
Output in SOQL:
```sql
CreatedDate <= 2010-8-12
```
---
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("dataset", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [2015, 8, 1], [2021, 3, 1]]
```

Output in standard dataset:
```sql
WHERE ("CreatedDate" >= DATE '2015-8-1' AND CreatedDate <= '2021-3-1'))
```
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("snowflake", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [2015, 8, 1], [2021, 3, 1]]
```

Output in Snowflake live dataset:
```sql
WHERE ("CreatedDate" BETWEEN to_date('2015-8-1') AND to_date('2021-3-1'))
```
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("sobject", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", [2015, 8, 1], [2021, 3, 1]]
```
Output in SOQL:
```sql
WHERE (CreatedDate >= 2015-8-1 AND CreatedDate <= 2021-3-1))
```
---
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("snowflake", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", ["quarter", -2], ["quarter", 3]
```

Output in Snowflake live dataset:
```sql
WHERE ("CreatedDate" >= date_trunc(quarter, dateadd(quarter, -2, current_date()))AND "CreatedDate" <>= date_trunc(quarter, dateadd(quarter, 3, current_date())))
```
Function:
```javascript
(toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])).asSQLWhere("sobject", true))
toArray("CreatedDate", "BETWEEN", column(stepFoo.selection, ["CreatedDate"])) =>  ["CreatedDate", "BETWEEN", ["quarter", -2], ["quarter", 3]
```
Output in SOQL:
```sql
WHERE (CreatedDate >= LAST_N_QUARTERS:2 AND CreatedDate <>= NEXT_N_QUARTERS:3)
```