N/query Module

Use the N/query module to create and run queries using the SuiteAnalytics Workbook query engine. For more information about SuiteAnalytics Workbook, see SuiteAnalytics Workbook Overview.

Using the query module, you can:

• Use multilevel joins to create queries using field data from multiple record types.

• Create conditions (filters) using AND, OR, and NOT logic, as well as formulas and relative dates.

• Sort query results based on the values of multiple columns.

• Load and delete existing saved queries that were created using the SuiteAnalytics Workbook interface.

• View paged query results.

• Use promises for asynchronous execution.

• Convert query objects to SuiteQL queries and run arbitrary SuiteQL queries.

For more information about creating scripts using the N/query module, see the following help topics:

• Scripting with the N/query Module

• Formulas in the N/query Module

• Relative Dates in the N/query Module

• SuiteQL in the N/query Module

Important:

As you use the N/query module, keep the following considerations in mind:

• The N/query module lets you create and run queries using the SuiteAnalytics Workbook query engine. You can use the N/query module to load and delete existing queries, but you cannot save queries. You can save queries using the SuiteAnalytics Workbook interface. For more information, see Navigating SuiteAnalytics Workbook.

• The N/query module uses a different data source than the N/search module. If you need to determine the ID of a record type or field (for example, to use as a query filter or result column), you must obtain this value from the Records Catalog, not the SuiteScript Records Browser. The Records Catalog lists every record type and field that is currently available in SuiteAnalytics Workbook and the N/query module. For more information, see Records Catalog Overview.

In This Help Topic

• N/query Module Members

• Column Object Members

• Component Object Members

• Condition Object Members

• Page Object Members

• PagedData Object Members

• PageRange Object Members

• Query Object Members

• RelativeDate Object Members

• Result Object Members

• ResultSet Object Members

• Sort Object Members

• SuiteQL Object Members

N/query Module Members

Member Type	Name	Return Type / Value Type	Supported Script Types	Description
Object	query.Column	Object	Client and server scripts	The field types (query result columns) that are displayed from the query results. Use Query.createColumn(options) or Component.createColumn(options) to create this object.
	query.Component	Object	Client and server scripts	One component of the query definition. The query definition always contains at least one component that encapsulates the initial query type. Queries with joins contain multiple components that encapsulate the join relationships. The initial component (Query.root) is automatically created with the query definition (query.Query). Use Query.autoJoin(options) or Component.autoJoin(options) to create subsequent components.
	query.Condition	Object	Client and server scripts	A condition. A condition narrows the query results. Use Query.createCondition(options) or Component.createCondition(options) to create this object.
	query.Page	Object	Client and server scripts	One page of the paged query results.
	query.PagedData	Object	Client and server scripts	A set of paged query results. This object also contains information about the set of paged results it encapsulates.
	query.PageRange	Object	Client and server scripts	A range of pages from the paged query results.
	query.Period	Object	Client and server scripts	A period of time to use in query conditions.
	query.RelativeDate	Object	Client and server scripts	A relative date to use in query conditions.
	query.Result	Object	Client and server scripts	A single row of the query result set.
	query.ResultSet	Object	Client and server scripts	The set of results returned by the query.
	query.Query	Object	Client and server scripts	The query definition. Use query.create(options) or query.load(options) to create this object. The creation of this object is the first step in creating a query with the N/query Module.
	query.Sort	Object	Client and server scripts	A sort that is placed on a particular query result column. Use Query.createSort(options) or Component.createSort(options) to create this object.
	query.SuiteQL	Object	Client and server scripts	A SuiteQL query. Use Query.toSuiteQL() to create this object.
Method	query.create(options)	query.Query	Client and server scripts	Creates the query definition. The execution of this method is the first step in creating a query with the N/query Module.
	query.createPeriod(options)	query.Period	Client and server scripts	Creates a query.Period object that represents a period of time.
	query.createRelativeDate(options)	query.RelativeDate	Client and server scripts	Creates a query.RelativeDate object that represents a date relative to the current date.
	query.delete(options)	void	Client and server scripts	Deletes an existing query that was created using the SuiteAnalytics Workbook UI. The deleted query is no longer available and cannot be modified or executed.
	query.listTables(options)	Array<Object>	Client and server scripts	Lists the table view objects that are included in a workbook in SuiteAnalytics Workbook.
	query.load(options)	query.Query	Client and server scripts	Loads an existing query that was created using the SuiteAnalytics Workbook UI. The loaded query can be modified (for example, by setting additional property values), joined with other query types, and executed in the same way as queries created using query.create(options).
	query.load.promise(options)	Promise Object	Client and server scripts	Asynchronously loads an existing query that was created using the SuiteAnalytics Workbook UI.
	query.runSuiteQL(options)	query.ResultSet	Client and server scripts	Runs an arbitrary SuiteQL query.
	query.runSuiteQL.promise(options)	Promise Object	Client and server scripts	Asynchronously runs an arbitrary SuiteQL query.
	query.runSuiteQLPaged(options)	query.PagedData	Client and server scripts	Runs an arbitrary SuiteQL query as a paged query.
	query.runSuiteQLPaged.promise(options)	Promise Object	Client and server scripts	Asynchronously runs an arbitrary query as a paged query.
Enum	query.Aggregate	enum	Client and server scripts	Holds the string values for aggregate functions supported with the N/query Module. This enum is used to pass the aggregate function argument to Component.createColumn(options), Component.createCondition(options), Query.createColumn(options), and Query.createCondition(options).
	query.DateId	enum	Client and server scripts	Holds the string values for supported date codes in relative dates. This enum is used to pass the date ID argument to query.createRelativeDate(options).
	query.FieldContext	enum	Client and server scripts	Holds the string values for the field context to use when creating a column. This enum is used to pass the context argument to Query.createColumn(options) and Component.createColumn(options).
	query.Operator	enum	Client and server scripts	Holds the string values for operators supported with the N/query Module. This enum is used to pass the operator argument to Query.createCondition(options) and Component.createCondition(options).
	query.PeriodAdjustment	enum	Client and server scripts	Holds the string values for adjustment types for a period. This enum is used to pass the adjustment argument to query.createPeriod(options).
	query.PeriodCode	enum	Client and server scripts	Holds the string values for period codes for a period. This enum is used to pass the code argument to query.createPeriod(options).
	query.PeriodType	enum	Client and server scripts	Holds the string values for period types for a period. This enum is used to pass the type argument to query.createPeriod(options).
	query.RelativeDateRange	enum	Client and server scripts	Holds query.RelativeDate object values for supported date ranges in relative dates. This enum is used to pass the values argument to Query.createCondition(options) and Component.createCondition(options).
	query.ReturnType	enum	Client and server scripts	Holds the string values for the formula return types supported with the N/query Module. This enum is used to pass the formula return type argument to Query.createColumn(options), Component.createColumn(options), Query.createCondition(options), and Component.createCondition(options).
	query.SortLocale	enum	Client and server scripts	Holds the string values for sort locales supported with the N/query Module. This enum is used to pass the sort locale argument to Query.createSort(options) and Component.createSort(options).
	query.Type	enum	Client and server scripts	Holds the string values for supported query types used in the query definition. This enum is used to pass the initial query type argument to query.create(options).

Column Object Members

The following members are available for a query.Column object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Property	Column.aggregate	string (read-only)	Client and server scripts	An aggregate function that is performed on the query result column. An aggregate function performs a calculation on the column values and returns a single value.
	Column.alias	string (read-only)	Client and server scripts	An alias for this column. An alias is an alternate name for a column, and the alias is used in mapped results.
	Column.component	query.Component (read-only)	Client and server scripts	A reference to the query.Component object to which this query result column belongs.
	Column.context	Object (read-only)	Client and server scripts	The field context for values in the query result column. The field context determines how field values are displayed in the column.
	Column.fieldId	string (read-only)	Client and server scripts	The name of the query result column. This property and the Column.formula property cannot be set at the same time.
	Column.formula	string (read-only)	Client and server scripts	The formula used to create the query result column. This property and the Column.fieldId property cannot be set at the same time.
	Column.groupBy	boolean (read-only)	Client and server scripts	Whether the query results are grouped by this query result column.
	Column.label	string (read-only)	Client and server scripts	The label for the column.
	Column.type	string (read-only)	Client and server scripts	The return type of the formula used to create the query result column.

Component Object Members

The following members are available for a query.Component object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Method	Component.autoJoin(options)	query.Component	Client and server scripts	Creates a join relationship. After you create the initial query definition, use Query.autoJoin(options) to create your first join. Then use this method to create each subsequent join. This method selects the correct join type automatically based on the record types that are being joined.
	Component.createColumn(options)	query.Column	Client and server scripts	Creates a query result column based on the component. Use this method to create columns based on the join relationships created with Query.autoJoin(options) and Component.autoJoin(options).
	Component.createCondition(options)	query.Condition	Client and server scripts	Creates a condition (filter column) based on the component. Use this method to create conditions based on the join relationships created with Query.autoJoin(options) and Component.autoJoin(options).
	Component.createSort(options)	query.Sort	Client and server scripts	Creates a sort based on the component. Use this method to create sorts based on the join relationships created with Query.autoJoin(options) and Component.autoJoin(options).
	Component.join(options)	query.Component	Client and server scripts	Creates a join relationship. This method is an alias to Component.autoJoin(options). After you create the initial query definition, use Query.autoJoin(options) to create your first join. Then use this method, or Component.autoJoin(options), to create each subsequent join.
	Component.joinFrom(options)	query.Component	Client and server scripts	Creates an explicit directional join relationship from another component to this component (an inverse join). This method sets the Component.source property on the returned query.Component object. After you create the initial query definition, use this method to create explicit directional joins from other components to this component.
	Component.joinTo(options)	query.Component	Client and server scripts	Creates an explicit directional join relationship to another component from this component (a polymorphic join). You can use this method to specify the target of the join when a field can join multiple query types. This method sets the Component.target property on the returned query.Component object. After you create the initial query definition, use this method to create explicit directional joins to other components from this component.
Property	Component.child	Object (read-only)	Client and server scripts	The child components of the component. This property holds an object of key-value pairs. Each key is the name of a child component. Each value is the corresponding child query.Component object.
	Component.parent	string (read-only)	Client and server scripts	The parent query.Component object of the component.
	Component.source	string (read-only)	Client and server scripts	The source query type of the component. The value of this property is set when Component.joinFrom(options) is called to perform an explicit directional join from another component.
	Component.target	string (read-only)	Client and server scripts	The target query type of the component. The value of this property is set when Component.joinTo(options) is called to perform an explicit directional join to another component.
	Component.type	string (read-only)	Client and server scripts	The query type of the component.

Condition Object Members

The following members are available for a query.Condition object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Property	Condition.aggregate	string (read-only)	Client and server scripts	An aggregate function that is performed on the condition. An aggregate function performs a calculation on the condition values and returns a single value.
	Condition.children	query.Condition[] (read-only)	Client and server scripts	An array of child conditions used to create the parent condition.
	Condition.component	query.Component (read-only)	Client and server scripts	A reference to the query.Component object to which this condition belongs.
	Condition.fieldId	string (read-only)	Client and server scripts	The name of the field that is used in the condition.
	Condition.formula	string (read-only)	Client and server scripts	The formula used to create the condition.
	Condition.operator	string (read-only)	Client and server scripts	The name of the operator used to create the condition.
	Condition.type	string (read-only)	Client and server scripts	The return type of the formula used to create the condition.
	Condition.values	string | number | boolean | Array<string | number | boolean> (read-only)	Client and server scripts	An array of values used by an operator to create the condition.

Page Object Members

The following members are available for a query.Page object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Property	Page.data	query.ResultSet (read-only)	Client and server scripts	The query results contained in this page.
	Page.isFirst	boolean (read-only)	Client and server scripts	Whether this page is the first of the paged query results.
	Page.isLast	boolean (read-only)	Client and server scripts	Whether this page is the last of the paged query results.
	Page.pagedData	query.PagedData (read-only)	Client and server scripts	The set of paged query results that this page is from.
	Page.pageRange	query.PageRange (read-only)	Client and server scripts	The range of query results for this page.

PagedData Object Members

The following members are available for a query.PagedData object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Method	PagedData.iterator()	Iterator object	Client and server scripts	Standard SuiteScript 2.0 object for iterating through results.
	PagedData.fetch(options)	query.Page	Client and server scripts	Retrieves a page in the set of pages included in the PagedData object.
	PagedData.fetch.promise(options)	Promise Object	Client and server scripts	Asynchronously retrieves a page in the set of pages included in the PagedData object.
Property	PagedData.count	number (read-only)	Client and server scripts	The total number of paged query results.
	PagedData.pageRanges	query.PageRange[]	Client and server scripts	An array of page ranges for the set of paged query results.
	PagedData.pageSize	number (read-only)	Client and server scripts	The number of query result rows per page.

PageRange Object Members

The following members are available for a query.PageRange object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Property	PageRange.index	number (read-only)	Client and server scripts	The array index for this page range.
	PageRange.size	number (read-only)	Client and server scripts	The number of query result rows in this page range.

Period Object Members

The following members are available for a query.Period object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Property	Period.adjustment	string (read-only)	Client and server scripts	The adjustment of the period. This property uses values from the query.PeriodAdjustment enum.
	Period.code	string (read-only)	Client and server scripts	The code of the period. This property uses values from the query.PeriodCode enum.
	Period.type	string (read-only)	Client and server scripts	The type of the period. This property uses values from the query.PeriodType enum.

Query Object Members

The following members are available for a query.Query object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Method	Query.and(conditions)	query.Condition	Client and server scripts	Creates a new condition (a query.Condition object) that corresponds to a logical conjunction (AND) of the arguments passed to the method. The arguments must be one or more query.Condition objects.
	Query.autoJoin(options)	query.Component	Client and server scripts	Creates a join relationship. After you create the initial query definition, use this method to create your first join or subsequent joins from the root component of the query. This method selects the correct join type automatically based on the record types that are being joined.
	Query.createColumn(options)	query.Column	Client and server scripts	Creates a query result column based on the query.Query object. Use this method to create columns on the initial query definition created with query.create(options).
	Query.createCondition(options)	query.Condition	Client and server scripts	Creates a condition (filter column) based on the query.Query object. Use this method to create conditions on the initial query definition created with query.create(options).
	Query.createSort(options)	query.Sort	Client and server scripts	Creates a sort based on the query.Query object. The query.Sort object describes a sort that is placed on a particular query result column or condition.
	Query.join(options)	query.Component	Client and server scripts	Creates a join relationship. This method is an alias to Query.autoJoin(options). After you create the initial query definition, use this method, or Query.autoJoin(options), to create your first join.
	Query.joinFrom(options)	query.Component	Client and server scripts	Creates an explicit directional join relationship from another component to the root component of the search definition (an inverse join). This method sets the Component.source property on the returned query.Component object. After you create the initial query definition, use this method to create your first join as an explicit directional join from another component to this component.
	Query.joinTo(options)	query.Component	Client and server scripts	Creates an explicit directional join relationship to another component from this component (a forward join). You can use this method to specify the target of the join when a field can join multiple query types. This method sets the Component.target property on the returned query.Component object. After you create the initial query definition, use this method to create your first join as an explicit directional join to another component from this component.
	Query.not(condition)	query.Condition	Client and server scripts	Creates a new condition (a query.Condition object) that corresponds to a logical negation (NOT) of the argument passed to the method. The argument must be a query.Condition object.
	Query.or(conditions)	query.Condition	Client and server scripts	Creates a new condition (a query.Condition object) that corresponds to a logical disjunction (OR) of the arguments passed to the method. The arguments must be one or more query.Condition objects.
	Query.run()	query.ResultSet	Client and server scripts	Executes the query and returns the query result set.
	Query.run.promise()	query.ResultSet	Client and server scripts	Executes the query asynchronously and returns the query result set.
	Query.runPaged()	query.PagedData	Client and server scripts	Executes the query and returns a set of paged results.
	Query.runPaged.promise()	query.PagedData	Client and server scripts	Executes the query asynchronously and returns a set of paged results.
	Query.toSuiteQL()	query.SuiteQL	Client and server scripts	Converts this query.Query object to its corresponding SuiteQL representation.
Property	Query.child	Object (read-only)	Client and server scripts	A reference to children of the root component of the query definition. The value of this property is an object of key-value pairs. Each key is the name of a child component. Each respective value is the corresponding query.Component object.
	Query.columns	query.Column[]	Client and server scripts	An array of query result columns returned from the query. Before you execute the query, you must assign all created columns as array values to this property.
	Query.condition	query.Condition object	Client and server scripts	The parent condition that narrows the query results. Before you execute the query, you must assign your simple or complex conditions to this property.
	Query.id	number (read-only)	Client and server scripts	The ID of the query definition. This property has a value only for existing queries that are loaded using query.load(options). If you create a query using query.create(options) but do not save it, this property is null.
	Query.name	string (read-only)	Client and server scripts	The name of the query definition. This property has a value only for existing queries that are loaded using query.load(options). If you create a query using query.create(options) but do not save it, this property is null.
	Query.root	query.Component (read-only)	Client and server scripts	The root component of the query definition.
	Query.sort	query.Column[] (read-only)	Client and server scripts	An array of query result columns used for sorting.
	Query.type	string (read-only)	Client and server scripts	The query type of the initial query definition.

RelativeDate Object Members

The following members are available for a query.RelativeDate object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Property	RelativeDate.dateId	string (read-only)	Client and server scripts	The ID of the relative date.
	RelativeDate.end	Object (read-only)	Client and server scripts	The end point of the relative date.
	RelativeDate.interval	Object (read-only)	Client and server scripts	The interval of the relative date (from the RelativeDate.start point to the RelativeDate.end point).
	RelativeDate.isRange	boolean (read-only)	Client and server scripts	Whether the relative date represents a range of dates or a specific moment in time.
	RelativeDate.start	Object (read-only)	Client and server scripts	The start point of the relative date.
	RelativeDate.value	number (read-only)	Client and server scripts	The value of the relative date.

Result Object Members

The following members are available for a query.Result object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Method	Result.asMap()	Object	Client and server scripts	The query result as a mapped result.
Property	Result.values	Array<string | number | boolean | null> (read-only)	Client and server scripts	The result values.

ResultSet Object Members

The following members are available for a query.ResultSet object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Method	ResultSet.asMappedResults()	Object[]	Client and server scripts	Returns a query result set as an array of mapped results.
	ResultSet.iterator()	Iterator object	Client and server scripts	Standard SuiteScript 2.0 object for iterating through results.
Property	ResultSet.columns	query.Column[] (read-only)	Client and server scripts	An array of query result column references.
	ResultSet.results	query.Result[] (read-only)	Client and server scripts	An array of query.Result objects.
	ResultSet.types	string[] (read-only)	Client and server scripts	An array of the return types for ResultSet.results.

Sort Object Members

The following members are available for a query.Sort object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Property	Sort.ascending	boolean	Client and server scripts	Whether the sort direction is ascending.
	Sort.caseSensitive	boolean	Client and server scripts	Whether the sort is case sensitive. If a sort is case sensitive (and the sort direction is ascending), rows with column values that start with uppercase letters are listed before rows with column values that start with lowercase letters. If a sort is not case sensitive, uppercase and lowercase letters are treated the same.
	Sort.column	query.Column (read-only)	Client and server scripts	The query result column that the query results are sorted by.
	Sort.locale	string	Client and server scripts	The locale to use for the sort. A locale represents a combination of language and region, and it can affect how certain values (such as strings) are sorted.
	Sort.nullsLast	boolean	Client and server scripts	Whether query results with null values are listed at the end of the query results.

SuiteQL Object Members

The following members are available for a query.SuiteQL object.

Member Type	Name	Return Type/Value Type	Supported Script Types	Description
Method	SuiteQL.run()	query.ResultSet	Client and server scripts	Runs the SuiteQL query and returns the query results.
	SuiteQL.runPaged(options)	query.PagedData	Client and server scripts	Runs the SuiteQL query as a paged query and returns the paged query results.
Property	SuiteQL.columns	query.Column[]	Client and server scripts	Describes the result columns to be returned from the query.
	SuiteQL.params	Array<string | number | boolean> (read-only)	Client and server scripts	Contains the parameters for the query.
	SuiteQL.query	string (read-only)	Client and server scripts	Holds the string representation of the query.
	SuiteQL.type	string (read-only)	Client and server scripts	Describes the type of the query. This property uses values from the query.Type enum.

Related Topics

• SuiteScript 2.x Modules
• SuiteScript 2.x
• SuiteAnalytics Workbook Overview

General Notices