A newer version of this documentation is available.

View Latest

Data Types

  • reference
    +
    SQL++ supports many data types: MISSING, NULL, Boolean values, numeric values, string values, arrays, objects, and binary.

    These types are described in detail in their respective sections. While SQL++ does not support the Date data type, it does provide a robust set of functions to work with dates.

    MISSING

    Missing represents a missing name-value pair in a JSON document. If the referenced field does not exist, an empty result value is returned by the query. Missing is added as a literal expression, although it is not returned in the final results. It is omitted from objects, and is converted to null in result arrays.

    Because SQL++ is not constrained by a fixed schema, some objects and documents can contain fields that others do not contain; so a field can be present in one document and MISSING in another. (MISSING is not present in SQL, because every record in a table follows an identical schema.)

    NULL

    Nulls represent empty values using the keyword NULL. For example, a developer might initially set a field value to null by default until a user enters a value.

    Null values are also generated by certain operations, for example, when dividing by zero or passing arguments of the wrong type.

    Note that NULL is case insensitive. For example, null, NULL, Null, and nUll are all equivalent.

    Boolean

    This type can have a value of TRUE and FALSE. The values are case insensitive.

    Numbers

    Numbers can be a signed decimal number that can contain a fractional part. Numbers can also use E-notation.

    String values

    Strings include all Unicode characters and backslash escape characters. They are delimited by single quotation marks ('') or double quotation marks ("").

    Arrays

    Arrays are an ordered list of zero or more values. The values can be of any type. Arrays are enclosed in square brackets ( [ ] ). Commas separate each value.

    Here are some array examples: ["one", "two", "three"] and [1, 2, 3], and ["one", 2, "three"].

    Objects

    Objects consist of name-value pairs. The name must be a string, and the value can be any supported JSON data type.

    Objects are enclosed in curly braces ( { } ). Commas separate each pair. The colon (:) character separates the name from its value within each pair.

    Here are some object examples: {"age": 17}, {"fname": "Jane", "lname": "Doe"}

    All names must be strings and should be distinct from each other within that object.

    Binary

    When Couchbase Server is used as a key-value store and the value is a non-JSON value, the value is exposed as binary. SQL++ does not parse the binary data. The returned value only signifies that the value is a binary value. For example, "\u003cbinary (size_in_bytes b)\u003e", where size_in_bytes signifies the size of the binary data in bytes. You can use the TYPE() function to retrieve the type of the value as binary. Functions that return metadata such as META() still operate as expected. However, operations on binary data are limited.

    Collation

    Collation defines the sort order for data types. Here is the default ascending collation order used for SQL++ data types:

    • MISSING

    • NULL

    • FALSE

    • TRUE

    • number

    • string

    • array

    • object

    • binary (non-JSON)

    It is possible to specify a different order, and set the ordering of NULL and MISSING values. See ORDER BY clause for more information.

    Date

    SQL++ does not support the Date data type; however SQL++ does provide a full range of functions which you can use to manipulate dates. See Date Functions for more information.