Using ROQL Tabular Queries

You can use RightNow Object Query Language (ROQL) tabular (QueryCSV) queries to perform searches in the REST API. ROQL tabular query support is available by using the queryResults resource.

This topic contains the following sections:

ROQL Tabular Queries

ROQL tabular queries have the following characteristics:
  • They use the queryResults resource with ROQL syntax:
    https://your_site_interface/services/rest/connect/version/
    queryResults/?query=semicolon-separated ROQL queries
  • They return an array of collections containing matching rows for each input query.
  • They support multiple, semicolon-separated ROQL queries in the query parameter.

Using Regular Expressions

Starting with the February 2017 release, regular expression queries are supported with ROQL. Regular expression queries use the REGEXP operator and have the properties shown in the following table.

Property Description

Query type

Tabular only

Note:

Queries on long text fields aren't supported because of potential performance issues.

Database

Reporting only. You must include a USE REPORT statement in the query.

Connect Common Object Model (CCOM) version

1.3 and later

Maximum number of concurrent queries (ROQL_CONCURRENT_REGEX_QUERY_LIMIT)

5 (default = 0, disabled)

Maximum join size (MAX_JOIN_SIZE)

One-half of ROQL MAX_JOIN_SIZE (default = 2.5 million/5 million)

Note:

Regular expression queries are a controlled-availability feature. To enable REGEXP queries, the hidden ROQL_CONCURRENT_REGEX_QUERY_LIMIT configuration setting must be set to greater than 0 (maximum of 5). To have the feature enabled, file a request on Oracle Support or contact your Oracle account manager.

Regular expression query example

The following example shows the use of regular expression queries. This query finds email addresses that start with "k" and end in "m."

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=USE REPORT;SELECT id, emails.address FROM contacts WHERE emails.address REGEXP '^k' AND
emails.address REGEXP 'm$';

Regular expression response example

One result is returned:

{
  "items": [
    {
      "tableName": "contacts",
      "count": 1,
      "columnNames": [
        "id",
        "address"
      ],
      "rows": [
        [
          "1",
          "kitkat@example.com"
        ]
      ]
    }
  ],
  "links": [
    {
      "rel": "self",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=USE%20REPORT;
       SELECT%20id,%20%20emails.address%20%20FROM%20contacts%20WHERE%20emails.address%20REGEXP%20%27^k%27%20
       AND%20emails.address%20REGEXP%20%27m$%27;"
    },
    {
      "rel": "canonical",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults"
    },
    {
      "rel": "describedby",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/metadata-catalog/queryResults",
      "mediaType": "application/schema+json"
    }
  ]
}

Finding regular expression query limits

Use the following query to find the maximum number of concurrent queries and the maximum join size:

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=SELECT RoqlInformation.Concurrency.*, RoqlInformation.Maxjoinsize.* FROM SystemInformation;

The response shows the concurrencyLimit and rowLimit values:

{
  "items": [
    {
      "tableName": "systemInformation",
      "count": 1,
      "columnNames": [
        "condition",
        "concurrencyLimit",
        "condition",
        "rowLimit"
      ],
      "rows": [
        [
          "REGEXP",
          "5",
          "REGEXP",
          "2500000"
        ]
      ]
    }
  ],
  "links": [
    {
      "rel": "self",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults?
       query=SELECT%20RoqlInformation.Concurrency.*,%20RoqlInformation.Maxjoinsize.*%20FROM%20SystemInformation;"
    },
    {
      "rel": "canonical",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults"
    },
    {
      "rel": "describedby",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/metadata-catalog/queryResults",
      "mediaType": "application/schema+json"
    }
  ]
}

Error messages

The following errors from regular expression queries are logged to the info_log.xml file, and to the trace files if tracing is enabled:

  • "Current site configuration settings prevent execution of ROQL queries with the REGEXP operator."
  • "Too many ROQL queries with the REGEXP operator are running concurrently. Try again later."

ROQL Tabular Query Example

Using GET with the following query:

https://mysite.example.com/services/rest/connect/v1.4/queryResults/
?query=select id, subject from incidents;

returns the following list of incidents:

{
    "items": [
      {
        "tableName": "incidents",
        "count": 63,
        "columnNames": [
            "id",
            "subject"
            ],
        "rows": [
            [
            "1",
            "How do I access my voicemail outside of my home calling area?"
            ],
            [
            "2",
            "Power surge recovery"
            ],
            [
            "3",
            "The wrong phones were shipped to my office"
            ],
            ...
            [
            "61",
            "Phone plays sound for no reason"
            ],
            [
            "62",
            "Phone becomes very hot"
            ],
            [
            "63",
            "Military discount?"
            ]
        ]
      }
    ]
}