sql | References | ArcGIS Maps SDK for JavaScript

import { parseWhereClause, sqlIn, sqlBooleanLiteral, sqlNumericLiteral, sqlStringLiteral } from "@arcgis/core/core/sql.js";

const { parseWhereClause, sqlIn, sqlBooleanLiteral, sqlNumericLiteral, sqlStringLiteral } = await $arcgis.import("@arcgis/core/core/sql.js");

Since: ArcGIS Maps SDK for JavaScript 4.14

Creates a WhereClause expression that adheres to standardized SQL expressions. SQL expression is a combination of one or more values, operators and SQL functions that results in to a value.

See also

Standardized SQL functions in ArcGIS Online

Functions

parseWhereClause

Function

Parses the given where clause string and returns an instance of WhereClause when resolved. The WhereClause object can be used repeatedly against different features, to test if they individually meet the condition. The parser does not parse a whole SQL statement (ie. SELECT X… WHERE…). It only parses the expression after the where token.

Signature: parseWhereClause (clause: string, fieldsIndex?: FieldsIndex | null | undefined): Promise<WhereClause>

Parameters

Parameter	Type	Description	Required
clause	string	The SQL where clause expression.
fieldsIndex	FieldsIndex \| null \| undefined	The fields index of the layer. The fields index is used to match the fields found in the where clause against the service, to fix casing for example.

Returns: Promise<WhereClause>
Parses a string where clause and returns a promise that resolves to an object with the WhereClause specification.

Examples

sql.parseWhereClause("POPULATION > 100000", layer.fieldsIndex)
.then(function(clause){
  let testResult = clause.testFeature(
    new Graphic({
      attributes: {
        POPULATION: 300000
      }
    })
  );
  console.log(testResult); // prints true
});

sql.parseWhereClause(
  "START_TIME BETWEEN TIMESTAMP '2023-01-01 00:00:00' AND TIMESTAMP '2023-12-31 23:59:59'",
  layer.fieldsIndex
)
.then(function(clause){
  const utcDate = Date.UTC(2023, 0, 1, 0, 0, 0);
  let testResult = clause.testFeature(
    new Graphic({
      attributes: {
        START_TIME: utcDate
      }
    })
  );
  console.log(testResult); // true
});

sqlIn

Function

Builds an SQL IN clause for the given field and list of values. Returns null if no values are provided.

Signature: sqlIn (fieldName: string, values: (string | number)[]): string | null | undefined

Parameters

Parameter	Type	Description	Required
fieldName	string	The name of the field on the table.
values	(string \| number)[]	The value of the field.

Returns: string | null | undefined

sqlBooleanLiteral

Function

Since: ArcGIS Maps SDK for JavaScript 5.0

Returns a standardized SQL boolean literal from a boolean value. Possible values are: TRUE, FALSE, and NULL.

Signature: sqlBooleanLiteral (value: boolean | null | undefined): string

Parameters

Parameter	Type	Description	Required
value	boolean \| null \| undefined	The boolean value to convert to a standardized SQL literal.

Returns: string
The resulting standardized SQL boolean literal.

sqlNumericLiteral

Function

Since: ArcGIS Maps SDK for JavaScript 5.0

Returns a standardized SQL numeric literal from a value.

Signature: sqlNumericLiteral (value: unknown, valueType?: AnyFieldType | null | undefined): string

Parameters

Parameter	Type	Description	Required
value	unknown	The value to convert to a standardized SQL numeric literal.
valueType	AnyFieldType \| null \| undefined	The field type used to interpret the provided value.

Returns: string
The resulting standardized SQL numeric literal. If the input is null, undefined, or cannot be converted to a finite number (for example, if it results in NaN, Infinity, or -Infinity), the function returns NULL.

Example

sqlNumericLiteral("123"); // returns "123"
sqlNumericLiteral(true);  // returns "1"
sqlNumericLiteral(null);  // returns "NULL"

sqlStringLiteral

Function

Since: ArcGIS Maps SDK for JavaScript 5.0

Returns a standardized SQL string literal from a value.

Signature: sqlStringLiteral (value: unknown, valueType?: AnyFieldType | null | undefined, timeZone?: TimeZone | null | undefined): string

Parameters

Parameter	Type	Description
value	unknown	The value to convert to a standardized SQL string literal.
valueType	AnyFieldType \| null \| undefined	The field type used to interpret the provided value.
timeZone	TimeZone \| null \| undefined	The time zone to use for formatting when the value is a date. Defaults to UTC.

Returns: string
The resulting standardized SQL string literal. Returns NULL if the value is null or undefined.

Example

sqlStringLiteral(new Date("2008-01-20T20:00:00-05:00")); // returns "'2008-01-21 01:00:00.000'"
sqlStringLiteral(true);  // returns "'true'"
sqlStringLiteral("quote'd"); // returns "'quote''d'"
sqlStringLiteral(1200877200000, establishedDateField); // returns "'2008-01-21 01:00:00.000'"
sqlStringLiteral(1200877200000, establishedDateField, "America/Toronto"); // returns "'2008-01-20 20:00:00.000'"

sqlDateLiteral

Function

Since: ArcGIS Maps SDK for JavaScript 5.0

Returns a standardized SQL date literal from a value.

Signature: sqlDateLiteral (value: unknown, valueType?: AnyFieldType | null | undefined, timeZone?: TimeZone | null | undefined): string

Parameters

Parameter	Type	Description
value	unknown	The value to convert to a standardized SQL date literal.
valueType	AnyFieldType \| null \| undefined	The field type used to interpret the provided value.
timeZone	TimeZone \| null \| undefined	The time zone to use for formatting when the value is a date. Defaults to UTC.

Returns: string
The resulting standardized SQL string literal. Returns NULL if the value is null or undefined.

Example

sqlDateLiteral(new Date("2008-01-20T20:00:00-05:00")); // returns "DATE'2008-01-21'"
sqlDateLiteral(1200877200000, dateField); // returns "DATE'2008-01-21'"
sqlDateLiteral(1200877200000, dateField, "America/Toronto"); // returns "DATE'2008-01-20'"

sqlTimeLiteral

Function

Since: ArcGIS Maps SDK for JavaScript 5.0

Returns a standardized SQL time literal from a value.

Signature: sqlTimeLiteral (value: unknown, valueType?: AnyFieldType | null | undefined, timeZone?: TimeZone | null | undefined): string

Parameters

Parameter	Type	Description
value	unknown	The value to convert to a standardized SQL time literal.
valueType	AnyFieldType \| null \| undefined	The field type used to interpret the provided value.
timeZone	TimeZone \| null \| undefined	The time zone to use for formatting when the value is a date. Defaults to UTC.

Returns: string
The resulting standardized SQL time literal. Returns NULL if the value is null or undefined.

Example

sqlTimeLiteral(1200877200000); // returns "TIME'01:00:00.000'"
sqlTimeLiteral(new Date("2008-01-20T20:00:00-05:00"), dateField); // returns "TIME'01:00:00.000'"
sqlTimeLiteral(1200877200000, dateField, "America/Toronto"); // returns "TIME'20:00:00.000'"
sqlTimeLiteral("2008-01-20T20:00:00.000-05:00", timestampOffsetField); // returns "TIME'20:00:00.000'",

sqlTimestampLiteral

Function

Since: ArcGIS Maps SDK for JavaScript 5.0

Returns a standardized SQL timestamp literal from a value.

Signature: sqlTimestampLiteral (value: unknown, valueType?: AnyFieldType | null | undefined, timeZone?: TimeZone | null | undefined): string

Parameters

Parameter	Type	Description
value	unknown	The value to convert to a standardized SQL timestamp literal.
valueType	AnyFieldType \| null \| undefined	The field type used to interpret the provided value.
timeZone	TimeZone \| null \| undefined	The time zone to use for formatting when the value is a date. Defaults to UTC.

Returns: string
The resulting standardized SQL timestamp literal. Returns NULL if the value is null or undefined.

Example

sqlTimestampLiteral("2008-01-20 20:00:00 -05:00"); // returns "TIMESTAMP'2008-01-20 20:00:00 -05:00'"
sqlTimestampLiteral("2008-01-20T20:00:00-05:00", timestampOffsetField); // returns "TIMESTAMP'2008-01-20 20:00:00 -05:00'"
sqlTimestampLiteral(1200877200000); // returns "TIMESTAMP'2008-01-21 01:00:00.000'"
sqlTimestampLiteral(1200877200000, "date", "America/Toronto"); // returns "TIMESTAMP'2008-01-20 20:00:00.000'"

sqlTimestampOffsetLiteral

Function

Since: ArcGIS Maps SDK for JavaScript 5.0

Returns a standardized SQL timestamp literal with offset from a value.

Signature: sqlTimestampOffsetLiteral (value: unknown, valueType?: AnyFieldType | null | undefined, timeZone?: TimeZone | null | undefined): string

Parameters

Parameter	Type	Description
value	unknown	The value to convert to a standardized SQL timestamp literal.
valueType	AnyFieldType \| null \| undefined	The field type used to interpret the provided value.
timeZone	TimeZone \| null \| undefined	The time zone to use for formatting when the value is a date. Defaults to UTC.

Returns: string
The resulting standardized SQL timestamp literal. Returns NULL if the value is null or undefined.

Example

sqlTimestampOffsetLiteral("2008-01-20 20:00:00 -05:00"); // returns "TIMESTAMP'2008-01-20 20:00:00 -05:00'"
sqlTimestampOffsetLiteral("2008-01-20T20:00:00-05:00", timestampOffsetField); // returns "TIMESTAMP'2008-01-20 20:00:00 -05:00'"
sqlTimestampOffsetLiteral(1200877200000); // returns "TIMESTAMP'2008-01-21 01:00:00.000 +00:00'"
sqlTimestampOffsetLiteral(1200877200000, "date", "America/Toronto"); // returns "TIMESTAMP'2008-01-20 20:00:00.000 -05:00'"

sqlLiteral

Function

Since: ArcGIS Maps SDK for JavaScript 5.0

Returns a standardized SQL literal for the given value. If type and/or as are provided, they control how the value is interpreted and which literal format is produced.

Signature: sqlLiteral (value: unknown, type?: SqlTypeInfo | null | undefined, as?: SqlTypeInfo | null | undefined): string

Parameters

Parameter	Type	Description
value	unknown	The value to convert to a standardized SQL literal.
type	SqlTypeInfo \| null \| undefined	The field type info used to interpret the provided value.
as	SqlTypeInfo \| null \| undefined	The target field type info for the resulting SQL literal.

Returns: string
The resulting standardized SQL literal. Returns NULL if the value is null or undefined.

Example

sqlLiteral(123); // returns "123"
sqlLiteral(true);  // returns "TRUE"
sqlLiteral("quote'd"); // returns "'quote''d'"
sqlLiteral(new Date("2008-01-20T20:00:00-05:00")); // returns "TIMESTAMP'2008-01-21 01:00:00.000'"
sqlLiteral(1200877200000, { type: dateField }); // returns "TIMESTAMP'2008-01-21 01:00:00.000'"
sqlLiteral(
  1200877200000,
  { type: fieldsIndex.get("date_pst"), timeZone: fieldsIndex.getTimeZone("date_pst") },
); // returns "TIMESTAMP'2008-01-20 17:00:00.000'"
sqlLiteral(
  1200877200000,
  { type: fieldsIndex.get("date_pst"), timeZone: fieldsIndex.getTimeZone("date_pst") },
  { type: fieldsIndex.get("date_est"), timeZone: fieldsIndex.getTimeZone("date_est") },
); // returns "TIMESTAMP'2008-01-20 20:00:00.000'"
sql.sqlLiteral(
  1200877200000,
  { type: fieldsIndex.get("date_pst"), timeZone: fieldsIndex.getTimeZone("date_pst") },
  { type: "date-only" },
); // returns "DATE'2008-01-20'"

sqlName

Function

Since: ArcGIS Maps SDK for JavaScript 5.0

Returns a standardized SQL identifier literal for the given name, quoting and escaping it when needed.

Signature: sqlName (name: string): string

Parameters

Parameter	Type	Description	Required
name	string	The identifier name (for example, a field or table name) to format for use in an SQL expression.

Returns: string
The resulting standardized SQL identifier literal

Example

sqlName(field.name); // returns field name quoted if necessary

Type definitions

AnyFieldType

Type definition

Accepted field type input for SQL literal conversion functions.

Type: { type: FieldType | FieldTypeJSON; } | FieldType | FieldTypeJSON

SqlTypeInfo

Type definition

Since: ArcGIS Maps SDK for JavaScript 5.0

type

Property

Type: AnyFieldType | null | undefined

Since: ArcGIS Maps SDK for JavaScript 5.0

timeZone

Property

Type: TimeZone | null | undefined

Since: ArcGIS Maps SDK for JavaScript 5.0