---
title: Type Functions
description: This topic describes the builtin SQL++ for Capella Analytics type functions.
pubDate: 2026-08-17T09:53:44.266Z
antora:
  editUrl: https://github.com/couchbaselabs/docs-columnar/edit/main/modules/sqlpp/pages/8_builtin_type.adoc
  xref: xref:analytics:sqlpp:8_builtin_type.adoc[]
---

[Consult the llms.txt file for a full list of contents](/llms.txt)
[View original HTML](/analytics/sqlpp/8_builtin_type.html)

# Type Functions

> This topic describes the builtin SQL++ for Capella Analytics type functions. 

## [](#is%5Farray)is\_array

* Syntax:  
is_array(expr)
* Checks whether the given expression is an `array` value.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `Boolean` on whether the argument is an `array` value or not,
  * a `missing` if the argument is a `missing` value,
  * a `null` if the argument is a `null` value.
* Example:  
{  
  "a": is_array(true),  
  "b": is_array(false),  
  "c": isarray(null),  
  "d": isarray(missing),  
  "e": isarray("d"),  
  "f": isarray(4.0),  
  "g": isarray(5),  
  "h": isarray(["1", 2]),  
  "i": isarray({"a":1})  
};
* The expected result is:  
{ "a": false, "b": false, "c": null, "e": false, "f": false, "g": false, "h": true, "i": false }

The function has an alias `isarray`.

## [](#is%5Fmultiset)is\_multiset

* Syntax:  
is_multiset(expr)
* Checks whether the given expression is an `multiset` value.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `Boolean` on whether the argument is an `multiset` value or not,
  * a `missing` if the argument is a `missing` value,
  * a `null` if the argument is a `null` value.
* Example:  
{  
  "a": is_multiset(true),  
  "b": is_multiset(false),  
  "c": is_multiset(null),  
  "d": is_multiset(missing),  
  "e": is_multiset("d"),  
  "f": ismultiset(4.0),  
  "g": ismultiset(["1", 2]),  
  "h": ismultiset({"a":1}),  
  "i": ismultiset({{"hello", 9328, "world", [1, 2, null]}})  
};
* The expected result is:  
{ "a": false, "b": false, "c": null, "e": false, "f": false, "g": false, "h": false, "i": true }

The function has an alias `ismultiset`.

## [](#is%5Fatomic-is%5Fatom)is\_atomic (is\_atom)

* Syntax:  
is_atomic(expr)
* Checks whether the given expression is a value of a [primitive](10%5Fdata%5Ftype.html#PrimitiveTypes) type.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `Boolean` on whether the argument is a primitive type or not,
  * a `missing` if the argument is a `missing` value,
  * a `null` if the argument is a `null` value.
* Example:  
{  
  "a": is_atomic(true),  
  "b": is_atomic(false),  
  "c": isatomic(null),  
  "d": isatomic(missing),  
  "e": isatomic("d"),  
  "f": isatom(4.0),  
  "g": isatom(5),  
  "h": isatom(["1", 2]),  
  "i": isatom({"a":1})  
};
* The expected result is:  
{ "a": true, "b": true, "c": null, "e": true, "f": true, "g": true, "h": false, "i": false }

The function has three aliases: `isatomic`, `is_atom`, and `isatom`.

## [](#is%5Fboolean-is%5Fbool)is\_Boolean (is\_bool)

* Syntax:  
is_Boolean(expr)
* Checks whether the given expression is a `Boolean` value.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `Boolean` on whether the argument is a `Boolean` value or not,
  * a `missing` if the argument is a `missing` value,
  * a `null` if the argument is a `null` value.
* Example:  
{  
  "a": isBoolean(true),  
  "b": isBoolean(false),  
  "c": is_Boolean(null),  
  "d": is_Boolean(missing),  
  "e": isbool("d"),  
  "f": isbool(4.0),  
  "g": isbool(5),  
  "h": isbool(["1", 2]),  
  "i": isbool({"a":1})  
};
* The expected result is:  
{ "a": true, "b": true, "c": null, "e": false, "f": false, "g": false, "h": false, "i": false }

The function has three aliases: `isBoolean`, `is_bool`, and `isbool`.

## [](#is%5Fnumber-is%5Fnum)is\_number (is\_num)

* Syntax:  
is_number(expr)
* Checks whether the given expression is a numeric value.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `Boolean` on whether the argument is a `smallint`/`tinyint`/`integer`/`bigint`/`float`/`double`value or not,
  * a `missing` if the argument is a `missing` value,
  * a `null` if the argument is a `null` value.
* Example:  
{  
  "a": is_number(true),  
  "b": is_number(false),  
  "c": isnumber(null),  
  "d": isnumber(missing),  
  "e": isnumber("d"),  
  "f": isnum(4.0),  
  "g": isnum(5),  
  "h": isnum(["1", 2]),  
  "i": isnum({"a":1})  
};
* The expected result is:  
{ "a": false, "b": false, "c": null, "e": false, "f": true, "g": true, "h": false, "i": false }

The function has three aliases: `isnumber`, `is_num`, and `isnum`.

## [](#is%5Fobject-is%5Fobj)is\_object (is\_obj)

* Syntax:  
is_object(expr)
* Checks whether the given expression is a `object` value.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `Boolean` on whether the argument is a `object` value or not,
  * a `missing` if the argument is a `missing` value,
  * a `null` if the argument is a `null` value.
* Example:  
{  
  "a": is_object(true),  
  "b": is_object(false),  
  "c": isobject(null),  
  "d": isobject(missing),  
  "e": isobj("d"),  
  "f": isobj(4.0),  
  "g": isobj(5),  
  "h": isobj(["1", 2]),  
  "i": isobj({"a":1})  
};
* The expected result is:  
{ "a": false, "b": false, "c": null, "e": false, "f": false, "g": false, "h": false, "i": true }

The function has three aliases: `isobject`, `is_obj`, and `isobj`.

## [](#is%5Fstring-is%5Fstr)is\_string (is\_str)

* Syntax:  
is_string(expr)
* Checks whether the given expression is a `string` value.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `Boolean` on whether the argument is a `string` value or not,
  * a `missing` if the argument is a `missing` value,
  * a `null` if the argument is a `null` value.
* Example:  
{  
  "a": is_string(true),  
  "b": isstring(false),  
  "c": isstring(null),  
  "d": isstr(missing),  
  "e": isstr("d"),  
  "f": isstr(4.0),  
  "g": isstr(5),  
  "h": isstr(["1", 2]),  
  "i": isstr({"a":1})  
};
* The expected result is:  
{ "a": false, "b": false, "c": null, "e": true, "f": false, "g": false, "h": false, "i": false }

The function has three aliases: `isstring`, `is_str`, and `isstr`.

## [](#is%5Fnull)is\_null

* Syntax:  
is_null(expr)
* Checks whether the given expression is a `null` value.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `Boolean` on whether the variable is a `null` or not,
  * a `missing` if the input is `missing`.
* Example:  
{ "v1": is_null(null), "v2": is_null(1), "v3": is_null(missing) };
* The expected result is:  
{ "v1": true, "v2": false }

The function has an alias `isnull`.

## [](#is%5Fmissing)is\_missing

* Syntax:  
is_missing(expr)
* Checks whether the given expression is a `missing` value.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `Boolean` on whether the variable is a `missing` or not.
* Example:  
{ "v1": is_missing(null), "v2": is_missing(1), "v3": is_missing(missing) };
* The expected result is:  
{ "v1": false, "v2": false, "v3": true }

The function has an alias `ismissing`.

## [](#is%5Funknown)is\_unknown

* Syntax:  
is_unknown(expr)
* Checks whether the given variable is a `null` value or a `missing` value.
* Arguments:

  * `expr` : an expression of any type.
* Return Value:

  * a `boolean` on whether the variable is a `null`/`missing` value (`true`) or not (`` `false ``).
* Example:  
{ "v1": is_unknown(null), "v2": is_unknown(1), "v3": is_unknown(missing) };
* The expected result is:  
{ "v1": true, "v2": false, "v3": true }

The function has an alias `isunknown`.

## [](#to%5Farray)to\_array

* Syntax:  
to_array(expr)
* Converts input value to an `array` value
* Arguments:

  * `expr` : an expression
* Return Value:

  * `missing` if the argument is missing
  * `null` if the argument is `null`
  * if the argument is of `array` type then it is returned as is
  * if the argument is of `multiset` type then it is returned as an `array` with elements in an undefined order
  * otherwise an `array` containing the input expression as its single item is returned
* Example:  
{  
  "v1": to_array("asterix"),  
  "v2": to_array(["asterix"]),  
};
* The expected result is:  
{ "v1": ["asterix"], "v2": ["asterix"] }

The function has an alias `toarray`.

## [](#to%5Fatomic-to%5Fatom)to\_atomic (to\_atom)

* Syntax:  
to_atomic(expr)
* Converts input value to a [primitive](10%5Fdata%5Ftype.html#PrimitiveTypes) value
* Arguments:

  * `expr` : an expression
* Return Value:

  * `missing` if the argument is missing
  * `null` if the argument is `null`
  * if the argument is of primitive type then it is returned as is
  * if the argument is of `array` or `multiset` type and has only one element then the result of invoking to\_atomic() on that element is returned
  * if the argument is of `object` type and has only one field then the result of invoking to\_atomic() on the value of that field is returned
  * otherwise `null` is returned
* Example:  
{  
  "v1": to_atomic("asterix"),  
  "v2": to_atomic(["asterix"]),  
  "v3": to_atomic([0, 1]),  
  "v4": to_atomic({"value": "asterix"}),  
  "v5": to_number({"x": 1, "y": 2})  
};
* The expected result is:  
{ "v1": "asterix", "v2": "asterix", "v3": null, "v4": "asterix", "v5": null }

The function has three aliases: `toatomic`, `to_atom`, and `toatom`.

## [](#to%5Fboolean-to%5Fbool)to\_boolean (to\_bool)

* Syntax:  
to_boolean(expr)
* Converts input value to a `boolean` value
* Arguments:

  * `expr` : an expression
* Return Value:

  * `missing` if the argument is missing
  * `null` if the argument is `null`
  * if the argument is of `boolean` type then it is returned as is
  * if the argument is of numeric type then `false` if it's `0` or `NaN`, otherwise `true`
  * if the argument is of `string` type then `false` if it's empty, otherwise `true`
  * if the argument is of `array` or `multiset` type then `false` if its size is `0`, otherwise `true`
  * if the argument is of `object` type then `false` if it has no fields, otherwise `true`
  * a type error results for all other input types
* Example:  
{  
  "v1": to_boolean(0),  
  "v2": to_boolean(1),  
  "v3": to_boolean(""),  
  "v4": to_boolean("asterix")  
};
* The expected result is:  
{ "v1": false, "v2": true, "v3": false, "v4": true }

The function has three aliases: `toboolean`, `to_bool`, and `tobool`.

## [](#to%5Fbigint)to\_bigint

* Syntax:  
to_bigint(expr)
* Converts input value to an integer value
* Arguments:

  * `expr` : an expression
* Return Value:

  * `missing` if the argument is missing
  * `null` if the argument is `null`
  * if the argument is of `boolean` type, `1` if it's `true`, `0` if it's `false`
  * if the argument is of numeric integer type then it is returned as the same value of `bigint` type
  * if the argument is of numeric `float`/`double` type then it is converted to `bigint` type
  * if the argument is of `string` type and can be parsed as integer then that integer value is returned, otherwise `null`
  * `null` if the argument is of `array`/`multiset`/`object` type
  * a type error results for all other input types
* Example:  
{  
  "v1": to_bigint(false),  
  "v2": to_bigint(true),  
  "v3": to_bigint(10),  
  "v4": to_bigint(float("1e100")),  
  "v5": to_bigint(double("1e1000")),  
  "v6": to_bigint("20")  
};
* The expected result is:  
{ "v1": 0, "v2": 1, "v3": 10, "v4": 9223372036854775807, "v5": 9223372036854775807, "v6": 20 }

The function has an alias `tobigint`.

## [](#to%5Fdouble)to\_double

* Syntax:  
to_double(expr)
* Converts input value to a `double` value
* Arguments:

  * `expr` : an expression
* Return Value:

  * `missing` if the argument is missing
  * `null` if the argument is `null`
  * if the argument is of `boolean` type, `1.0` if it is `true`, `0.0` if it is `false`
  * if the argument is of numeric type then it is returned as the value of `double` type
  * if the argument is of `string` type and can be parsed as `double` then that `double` value is returned, otherwise `null`
  * `null` if the argument is of `array`/`multiset`/`object` type
  * a type error results for all other input types
* Example:  
{  
  "v1": to_double(false),  
  "v2": to_double(true),  
  "v3": to_double(10),  
  "v4": to_double(11.5),  
  "v5": to_double("12.5")  
};
* The expected result is:  
{ "v1": 0.0, "v2": 1.0, "v3": 10.0, "v4": 11.5, "v5": 12.5 }

The function has an alias `todouble`.

## [](#to%5Fnumber-to%5Fnum)to\_number (to\_num)

* Syntax:  
to_number(expr)
* Converts input value to a numeric value
* Arguments:

  * `expr` : an expression
* Return Value:

  * `missing` if the argument is missing
  * `null` if the argument is `null`
  * if the argument is of numeric type then it is returned as is
  * if the argument is of `boolean` type, `1` if it is `true`, `0` if it is `false`
  * if the argument is of `string` type and can be parsed as `bigint` then that `bigint` value is returned, otherwise if it can be parsed as `double` then that `double` value is returned, otherwise `null`
  * `null` if the argument is of `array`/`multiset`/`object` type
  * a type error results for all other input types
* Example:  
{  
  "v1": to_number(false),  
  "v2": to_number(true),  
  "v3": to_number(10),  
  "v4": to_number(11.5),  
  "v5": to_number("12.5")  
};
* The expected result is:  
{ "v1": 0, "v2": 1, "v3": 10, "v4": 11.5, "v5": 12.5 }

The function has three aliases: `tonumber`, `to_num`, and `tonum`.

## [](#to%5Fobject-to%5Fobj)to\_object (to\_obj)

* Syntax:  
to_object(expr)
* Converts input value to an `object` value
* Arguments:

  * `expr` : an expression
* Return Value:

  * `missing` if the argument is missing
  * `null` if the argument is `null`
  * if the argument is of `object` type then it is returned as is
  * otherwise an empty `object` is returned
* Example:  
{  
  "v1": to_object({"value": "asterix"}),  
  "v2": to_object("asterix")  
};
* The expected result is:  
{ "v1": {"value": "asterix"}, "v2": {} }

The function has three aliases: `toobject`, `to_obj`, and `toobj`.

## [](#to%5Fstring-to%5Fstr)to\_string (to\_str)

* Syntax:  
to_string(expr)
* Converts input value to a string value
* Arguments:

  * `expr` : an expression
* Return Value:

  * `missing` if the argument is missing
  * `null` if the argument is `null`
  * if the argument is of `boolean` type then `"true"` is returned if it is `true`, `"false"` if it is `false`
  * if the argument is of numeric type then its string representation is returned
  * if the argument is of `string` type then it is returned as is
  * if the argument is of `array`/`multiset`/`object` type then `null` is returned
  * a type error results for all other input types
* Example:  
{  
  "v1": to_string(false),  
  "v2": to_string(true),  
  "v3": to_string(10),  
  "v4": to_string(11.5),  
  "v5": to_string("asterix")  
};
* The expected result is:  
{ "v1": "false", "v2": "true", "v3": "10", "v4": "11.5", "v5": "asterix" }

The function has three aliases: `tostring`, `to_str`, and `tostr`.

## [](#typename)typename

* Syntax:  
typename(expr)
* Returns the type of an expression.
* Arguments:

  * `expr`: an expression.
* Return Value:

  * Returns a string, depending on the type of `expr`: number, string, array, object, or boolean.
  * Returns NULL if `expr` is NULL.
* Example:  
{"v1": typename(123),  
 "v2": typename("abc"),  
 "v3": typename([1, 2, 3]),  
 "v4": typename({"abc": 123}),  
 "v5": typename(true)};
* The expected result is:  
{  
  "v1": "number",  
  "v2": "string",  
  "v3": "array",  
  "v4": "object",  
  "v5": "boolean"  
}

## [](#array%5Finfer%5Fschema)array\_infer\_schema

* Syntax:  
array_infer_schema(collection[, parameters])
* Infers the schema of an array or multiset, for example the structure of the elements, data types of various attributes, sample values, and so on. Since an array or multiset can contain items with varying structures, the result of this function is statistical in nature rather than deterministic.  
This function is the equivalent to the SQL++ for Query [INFER statement](../n1ql/n1ql-language-reference/infer.html).  
> [!NOTE]  
> You can infer the schema of a collection by applying this function to a subquery which returns the documents in that collection, or a representative sample of them. The subquery must use the `SELECT VALUE` clause to avoid an additional layer of nesting in the result of the subquery.
* Arguments:

  * `collection`: An array or multiset, or an expression that evaluates to an array or multiset.
  * `parameters`: Optional. An object, which may contain one or more of the following fields to guide the function.

    * `similarity_metric`: Optional. A number, or an expression that evaluates to a number, between 0 and 1\. This indicates the percentage match of attributes required for two schemas to have the same flavor. If omitted, it defaults to 0.6.
    * `num_sample_values`: Optional. An integer, or an expression that evaluates to an integer. This indicates the maximum number of sample values to be returned for each attribute, providing examples of the data format. If omitted, it defaults to 5.
* Return Value:

  * An array of one or more objects, each of which contains an inferred schema in [JSON Schema](http://json-schema.org/documentation.html)format. For details of the schema, refer to the SQL++ for Query [INFER statement](../n1ql/n1ql-language-reference/infer.html).
  * Returns an empty array if `collection` is MISSING or NULL.
  * Returns an error if `collection` is not an array or multiset.
  * Returns an error if `parameters` is not an object.
  * Returns a warning if `similarity_metric` is not a number.
  * Returns a warning if `num_sample_values` is not a number.
  * Returns a warning if the argument name provided is not recognized by array\_infer\_schema
* Example 1:  
Infer schemas from an array or multiset.  
array_infer_schema([{"a": 1},{"a":"aval"},{"a":[1,2]}], {"similarity_metric": 0.6});
* The expected result is:  
[  
  {  
    "#docs": 1,  
    "%docs": 33.33333333333333,  
    "type": "object",  
    "Flavor": "",  
    "properties": {  
      "a": {  
        "#docs": 1,  
        "%docs": 100,  
        "type": "array",  
        "samples": [  
          [  
            1,  
            2  
          ]  
        ],  
        "maxItems": 2,  
        "minItems": 2,  
        "items": "number"  
      }  
    }  
  },  
  {  
    "#docs": 1,  
    "%docs": 33.33333333333333,  
    "type": "object",  
    "Flavor": "'a' = \"aval\"",  
    "properties": {  
      "a": {  
        "#docs": 1,  
        "%docs": 100,  
        "type": "string",  
        "samples": [  
          "aval"  
        ]  
      }  
    }  
  },  
  {  
    "#docs": 1,  
    "%docs": 33.33333333333333,  
    "type": "object",  
    "Flavor": "'a' = 1",  
    "properties": {  
      "a": {  
        "#docs": 1,  
        "%docs": 100,  
        "type": "number",  
        "samples": [  
          1  
        ]  
      }  
    }  
  }  
]  
The function detects that the input data has three flavors of document: one where `a` is 1, one where `a` is `"aval"`, and one where `a` is an array. All documents are objects, and each document only has the `a` property.
* Example 2:  
Infer the schema of the `customers` collection using a subquery.  
array_infer_schema((SELECT VALUE c FROM customers as c), {"num_sample_values": 3});
* The expected result is:  
[  
  {  
    "#docs": 7,  
    "%docs": 100,  
    "type": "object",  
    "Flavor": "",  
    "properties": {  
      "address": {  
        "#docs": 7,  
        "%docs": 100,  
        "type": "object",  
        "samples": [  
          {  
            "street": "690 River St.",  
            "city": "Hanover, MA",  
            "zipcode": "02340"  
          },  
          {  
            "street": "201 Main St.",  
            "city": "St. Louis, MO",  
            "zipcode": "63101"  
          },  
          {  
            "street": "120 Harbor Blvd.",  
            "city": "Boston, MA",  
            "zipcode": "02115"  
          }  
        ],  
        "properties": {  
          "city": {  
            "#docs": 7,  
            "%docs": 100,  
            "type": "string",  
            "samples": [  
              "Boston, MA",  
              "Hanover, MA",  
              "St. Louis, MO"  
            ]  
          },  
          "street": {  
            "#docs": 7,  
            "%docs": 100,  
            "type": "string",  
            "samples": [  
              "201 Main St.",  
              "690 River St.",  
              "120 Harbor Blvd."  
            ]  
          },  
          "zipcode": {  
            "#docs": 6,  
            "%docs": 85.71428571428571,  
            "type": "string",  
            "samples": [  
              "02115",  
              "02340",  
              "63101"  
            ]  
          }  
        }  
      },  
      "custid": {  
        "#docs": 7,  
        "%docs": 100,  
        "type": "string",  
        "samples": [  
          "C13",  
          "C25",  
          "C37"  
        ]  
      },  
      "name": {  
        "#docs": 7,  
        "%docs": 100,  
        "type": "string",  
        "samples": [  
          "T. Cody",  
          "T. Henry",  
          "M. Sinclair"  
        ]  
      },  
      "rating": {  
        "#docs": 6,  
        "%docs": 85.71428571428571,  
        "type": "number",  
        "samples": [  
          640,  
          690,  
          750  
        ]  
      }  
    }  
  }  
]  
The function detects that this collection has only one flavor of document. All documents are objects, and each document has the following properties: `custid` (string), `name` (string), `rating` (number), and `address` (object), which in turn contains the properties `city` (string), `street` (string), and `zipcode` (string).