v1

latestOpenAPI 3.1.02026-08-04170116.0 KB

Data Out

Execute Custom SQL Query

Run custom SQL queries for structured data retrieval. The response returns the query results in a JSON format.

post/orcabase/api/sql

Request body

shopIdstring required

The shop domain for which you are requesting data (e.g., example.myshopify.com).

querystring required

The SQL query string to execute. Include dynamic @startDate AND @endDate parameters in your query string, and provide specific start and end values in the request body via period.startDate and period.endDate.

currencystring

The currency for data aggregation (e.g., USD, EUR). Defaults to the currency provided during user account registration.

Example request

{
  "shopId": "example.myshopify.com",
  "query": "SELECT SUM(spend) AS spend, channel FROM ads_table a WHERE event_date BETWEEN @startDate AND @endDate GROUP BY channel",
  "currency": "USD",
  "period": {
    "startDate": "2025-07-01",
    "endDate": "2025-07-31"
  }
}

Response

The SQL query was executed successfully, and the response contains the query results in a JSON format grouped by channel and event_date.

successboolean

Indicates whether the request was successful.

messagestring

A success message indicating the query was executed.

dataobject[]

The query results as an array of objects, with dynamically generated properties that can include strings, numbers, dates, or other types.

Example response

{
  "success": true,
  "message": "The query was executed successfully.",
  "data": [
    {
      "channel": "facebook-ads",
      "event_date": "2024-12-01",
      "spend": 345,
      "clicks": 1250
    },
    {
      "channel": "google-ads",
      "event_date": "2024-12-02",
      "spend": 412.75,
      "clicks": 900
    },
    {
      "channel": "twitter-ads",
      "event_date": "2024-12-03",
      "spend": 210.25,
      "clicks": 640
    }
  ]
}