Query syntax of custom object records

Knowledge Center > Zuora Central > Data Model > Custom object records > Query syntax of custom object records

Query syntax of custom object records

The Data Model feature is in Limited Availability. If you want to have access to the feature, submit a request at Zuora Global Support.

You can use the Query custom object records API call to query custom object records. You can define your query through the q query parameter of this operation.

See the following query syntax for the q parameter.

Simple Query

Field Existence

Check existence of a field by querying with _exists_:[FieldName].

  • Search for custom fields data where the title field has non-null value (the field exists and the value of it is not null).

      _exists_:title

Field Equality

Check whether the value of a field equals to a certain value by querying with [FieldName]:[ExpectedValue]

  • Search for custom fields data with field status is active.

      status:active
  • Search for custom fields data with field author identical to phrase "john smith".

      author:"John Smith"

Ranges

Ranges can be specified for date and numeric fields. See the following statement syntax:

  • Less than: [FieldName]:<[Upperbound]

  • Less than or equal to: [FieldName]:<=[Upperbound]

  • Greater than: [FieldName]:>[Lowerbound]

  • Greater than or equal to: [FieldName]:>=[Lowerbound]

Number

  • Search for custom fields data with field age less than 5.

      age:<5

Date

  • Search for custom fields data with field activeDate less than 2018-01-01T00:00:00.001Z.

      activeDate:<2018-01-01T00:00:00.001Z
  • Search for custom fields data with field activeDate less than 2018-01-01T00:00:00Z.

      activeDate:<2018-01-01T00:00:00Z
  • Search for custom fields data with field activeDate less than 2018-01-01T00:00:00-08:00.

      activeDate:<2018-01-01T00:00:00-08:00
  • Search for custom fields data with field activeDate less than 2018-01-01.

      activeDate:<2018-01-01
  • Search for custom fields data with field activeDate less than 2018-01 (same as 2018-01-01).

      activeDate:<2018-01
  • Search for custom fields data with field activeDate less than 2018 (same as 2018-01-01).

      activeDate:<2018
  • Search for custom fields data with field activeDate less than 1514764800000 (2018-01-01 in milliseconds).

      activeDate:<1514764800000

Note:

  • Range query by a number on a date field compares field values as a timestamp in milliseconds except that the number is a 4-digit number. In that case, the field values will be considered as the value of a year.

    • activeDate:<1 will return custom fields data with activeDate less than 1970-01-01T00:00:00.001Z (0000000000001).

    • activeDate:<2001 will return custom fields data with activeDate less than 2001-01-01.

  • Range query by date only (without time) on date field with full date (with time) compares only their date part.

    • activeDate:>2018 will not return custom fields data with activeDate has value 2018-01-01T23:59:59Z since their dates are equal. But it will return custom fields data with activeDate is 2018-01-02.
  • Range query by year only works the same as by the first day of that year. Range query by year-month works the same as by the first day of the month.

    • activeDate:>2018 works the same as activeDate:>2018-01-01.

    • activeDate:>2018-05 works the same as activeDate:>2018-05-01.

  • Range query by full date (with time) on date field with date only (without time) will assume the time of the date field is 00:00:00.000Z.

    • activeDate:<2018-01-01T00:00:00.001Z will return custom fields data with activeDate value as 2018-01-01.

Complex Query Using Boolean Operators

Custom object query currently supports the AND (&&)OR (||)and NOT (!) operators. Note that the operators are case-sensitive. You must use uppercase.

AND

  • Search for custom fields data whose status is active and activeDate is less than 2018.

      status:active AND activeDate:<2018

    or

      status:active && activeDate:<2018

OR

  • Search for custom fields data whose status is active or activeDate is less than 2018.

      status:active OR activeDate:<2018

    or

      status:active || activeDate:<2018

NOT

  • Search for custom fields data whose status is missing or status has a null value.

      !_exists_:status

    or

    NOT _exists_:status
  • Search for custom fields data whose status is not active (status is either missing or null).

      !status:active 

    or

      NOT status:active 

    Note: To search for custom fields data where status exists and its value not equal to active, use _exists_:status AND !status:active.

  • Search for custom fields data with field activeDate no less than 2018 (including activeDate is missing or null).

      !activeDate:<2018 

    or

      NOT activeDate:<2018 

    Note: To search for custom fields data where activeDate exists and its value is no less than 2018, use _exists_:activeDate AND !activeDate:<2018.

Deal with the null value

The null value fields cannot be distinguished from the missing fields. Therefore, when querying by the existence of a field, it only returns the records that contain the field and the field value is not null. Query by the non-existence of one field will return the records that either miss this field or have a null value in this field.

Last modified

Tags

This page has no custom tags.

Classifications

(not set)