Salesforce Docs Portal

Retrieve Data with Query V2

Use Query API V1 to query Data 360 data lake across data model, data lake, unified, and linked objects.

note: A newer version of the Query API is available. We recommend using the newest Query API.

Syntax

  • HTTP Method: POST
  • Availability: Data 360 v1.0, Salesforce v51.0
  • format: REST
  • URI: /api/v1/query

Query Parameters

Parameter Type Description
limit integer Maximum number of records to return. Default: 49,999. Example: 10
offset integer Number of records to skip before returning results. Example: 100
orderby string Comma-separated values to sort the results in ascending or descending order. Example: GenderId__c ASC, Occupation__c DESC

Request Body

The request body must contain a JSON object with these fields:

Field Name Field Type Description
sql string The SQL query to execute. Must be a valid ANSI standard SQL query.

Response Structure

The response includes:

  • data: Array of record objects containing the requested data
  • startTime: ISO 8601 timestamp indicating when the query started executing
  • endTime: ISO 8601 timestamp indicating when the query finished executing
  • rowCount: Number of rows returned by the query
  • queryId: Unique identifier for the query
  • done: boolean indicating if all results have been returned
  • metadata: Object containing metadata about the fields in the result set

Metadata Object

The metadata object contains information about each field on the result set:

Field Name Field Type Description
type string SQL data type of the field
placeInOrder integer Position of the field on the result set
typeCode integer Numeric code representing the SQL data type

Examples

Request

POST https://{TSE}.360a.salesforce.com/api/v1/query
Authorization: Bearer {{cdpAuthToken}}
Content-Type: application/json

{
  "sql": "select BirthDate__c, DataSourceId__c, DataSourceObjectId__c, ExternalRecordId__c, FirstName__c, GenderId__c, Id__c, InternalOrganizationId__c, LastName__c from Individual__dlm where Id__c='100470169'"
}

Response

{
  "data": [
    {
      "Id__c": "100470169",
      "GenderId__c": "M",
      "FirstName__c": "Joshua",
      "LastName__c": "Carrier",
      "BirthDate__c": "2022-07-12T06:41:47.865044Z",
      "ExternalRecordId__c": "003f200002a3227163",
      "DataSourceId__c": "SubscribedUsers",
      "InternalOrganizationId__c": "",
      "DataSourceObjectId__c": "Subscribers_for_CDP"
    }
  ],
  "startTime": "2020-12-19T06:50:05.251Z",
  "endTime": "2020-12-19T06:50:05.251Z",
  "rowCount": 1,
  "queryId": "3442db1a-ecef-42b3-b0d8-971ee459efa4",
  "done": true,
  "metadata": {
    "BirthDate__c": {
      "type": "TIMESTAMP WITH TIME ZONE",
      "placeInOrder": 3,
      "typeCode": 93
    },
    "LastName__c": {
      "type": "VARCHAR",
      "placeInOrder": 2,
      "typeCode": 12
    },
    "FirstName__c": {
      "type": "VARCHAR",
      "placeInOrder": 1,
      "typeCode": 12
    },
    "InternalOrganizationId__c": {
      "type": "VARCHAR",
      "placeInOrder": 5,
      "typeCode": 12
    },
    "Id__c": {
      "type": "VARCHAR",
      "placeInOrder": 0,
      "typeCode": 12
    },
    "DataSourceObjectId__c": {
      "type": "VARCHAR",
      "placeInOrder": 6,
      "typeCode": 12
    },
    "DataSourceId__c": {
      "type": "VARCHAR",
      "placeInOrder": 4,
      "typeCode": 12
    },
    "GenderId__c": {
      "type": "VARCHAR",
      "placeInOrder": 7,
      "typeCode": 12
    },
    "ExternalRecordId__c": {
      "type": "VARCHAR",
      "placeInOrder": 8,
      "typeCode": 12
    }
  }
}

Notes

  • The maximum number of rows returned is 49,999
  • Use the done flag to determine if more results are available
  • When done is false, use the orderby parameter with offset to retrieve the next set of records
  • The API supports ANSI standard SQL queries
  • All queries are executed synchronously
  • Pagination supports limit, offset, and orderby parameters:
    • When the done flag is true in the response, there are no more records to query
    • When the done flag is false, use orderby with offset to retrieve the next set of records

Related Resources