Queries (pull)

For when you want to pull data from Pitchly
Endpoints
Endpoint
Description
​databases​
Get all the databases that belong to an organization.
​database​
Get information about a specific database.
​data​
Get data rows from a database.
​dataCount​
Get the number of rows in a result.
​content​
Get images and PPT asset for selected rows.
Mutations (push)
Subscriptions (listen)
databases
Gets all the databases that belong to an organization. If docDbOnly is true, then only databases that have the Documents app installed will be returned.
You can get the organization ID from the URL address bar while logged into Pitchly. When inside the account, the URL will follow the pattern:
https://v2.pitchly.net/t/<ORGANIZATION_ID>/d/<DATABASE_ID>/a/<APP_ID>
GraphQL
REST
query databases($secretKey: String!, $organizationId: ID!, $docDbOnly: Boolean) {
  databases(secretKey: $secretKey, organizationId: $organizationId, docDbOnly: $docDbOnly) {
    _id
    name
    fields {
      _id
      name
      type
      primary
      required
      restrict
      database
    }
    filters {
      _id
      name
      filter
    }
    templates {
      path
      name
    }
  }
}
Variables:
{
  "secretKey": <SECRET_KEY>,
  "organizationId": <ORGANIZATION_ID>,
  "docDbOnly": Boolean
}
In the Body of the request:
{
  "operationName": "databases",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "organizationId": <ORGANIZATION_ID>,
    "docDbOnly": Boolean
  },
  "query": "query databases($secretKey: String!, $organizationId: ID!, $docDbOnly: Boolean) {\n  databases(secretKey: $secretKey, organizationId: $organizationId, docDbOnly: $docDbOnly) {\n    _id\n    name\n    fields {\n      _id\n      name\n      type\n      primary\n      required\n      restrict\n      database\n    }\n  }\n}\n"
}
With GraphQL, you can narrow down the properties you wish to receive in the result, such as only the fields in the tables, or even just the name and _id. filters and templates, however, may require some more explanation.
filters is an array of "Saved Filters" that have been saved in your Pitchly account in the left-hand pane to a database. It returns the name of the filter and the filter object that you can pass directly to the data query to load results matching that filter.
templates is an array of templates that live in the Documents app for this database. The path of the template is a string value containing the stringified path to the template in the directory structure in the Documents app, where each folder is separated by " > ". For example, a template called "Foo" in the folder "Bar" would read Bar > Foo. If the template is at the root directory, it would just read, Foo. The name property simply contains the name of the template, the equivalent of the last name shown in the path.
Example result
Returns an array of databases in the specified organization. Each database has an _id, name, and an array of fields. See the meaning of each field's properties here, and more information about field types.
Success
Error
{
  "data": {
    "databases": [
      {
        "_id": "uJL9XrebupC7eWvM3",
        "name": "Clients",
        "fields": [
          {
            "_id": "xEfek48BRpD3kAksh",
            "name": "Name",
            "type": "string",
            "primary": true,
            "required": null,
            "restrict": null,
            "database": null
          }
        ],
        "filters": [],
        "templates": [
          {
            "name": "(0) CloseDate Logo Counselto Description DealValue",
            "path": "Tombstones > (0) CloseDate Logo Counselto Description DealValue"
          }
        ]
      },
      {
        "_id": "LkfjQ9pq9f48K2vD8",
        "name": "Products",
        "fields": [
          {
            "_id": "ELHqkJAFiyRfzdMsP",
            "name": "Name",
            "type": "string",
            "primary": true,
            "required": null,
            "restrict": null,
            "database": null
          },
          {
            "_id": "MBadxhiHvf78yDpPS",
            "name": "In Stock",
            "type": "boolean",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": null
          }
        ],
        "filters": [],
        "templates": []
      },
      {
        "_id": "Ccx2Fzva28RshjFpr",
        "name": "Orders",
        "fields": [
          {
            "_id": "Nb6uqwKdxh3BFX3De",
            "name": "ID",
            "type": "string",
            "primary": true,
            "required": true,
            "restrict": null,
            "database": null
          },
          {
            "_id": "RyM8SyrYrceRsrLJd",
            "name": "Client",
            "type": "ref",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": "uJL9XrebupC7eWvM3"
          },
          {
            "_id": "g5WPqJugq9o3fQPXH",
            "name": "Products",
            "type": "refMultiple",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": "LkfjQ9pq9f48K2vD8"
          },
          {
            "_id": "Jex4vcwa9HZ4GipXg",
            "name": "Photo",
            "type": "attachment",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": null
          },
          {
            "_id": "pnR2Mnqfjdj9vQG8e",
            "name": "Amount",
            "type": "currency",
            "primary": null,
            "required": true,
            "restrict": null,
            "database": null
          },
          {
            "_id": "2ybyZfu6H6KNfGbBt",
            "name": "Location",
            "type": "enum",
            "primary": null,
            "required": null,
            "restrict": [
              "Houston",
              "Atlanta",
              "Las Vegas",
              "Minneapolis",
              "New York"
            ],
            "database": null
          },
          {
            "_id": "q6iD8LCHSjhJusLSd",
            "name": "Date",
            "type": "date",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": null
          },
          {
            "_id": "JYszfW2jpLeTck8mW",
            "name": "Department",
            "type": "enumTags",
            "primary": null,
            "required": null,
            "restrict": [
              "Electronics",
              "Cosmetics",
              "Cookware",
              "Hardware",
              "Home"
            ],
            "database": null
          },
          {
            "_id": "hMQKzyxRBxK4sGN5M",
            "name": "Quantity",
            "type": "number",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": null
          }
        ],
        "filters": [],
        "templates": []
      }
    ]
  }
}
{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}
database
Gets information about a specific database, including its fields.
You can get the database ID from the URL address bar while logged into Pitchly. When inside the account, the URL will follow the pattern:
https://v2.pitchly.net/t/<ORGANIZATION_ID>/d/<DATABASE_ID>/a/<APP_ID>
GraphQL
REST
query database($secretKey: String!, $id: ID!) {
  database(secretKey: $secretKey, id: $id) {
    _id
    name
    fields {
      _id
      name
      type
      primary
      required
      restrict
      database
    }
    filters {
      _id
      name
      filter
    }
    templates {
      path
      name
    }
  }
}
Variables:
{
  "secretKey": <SECRET_KEY>,
  "id": <DATABASE_ID>
}
In the Body of the request:
{
  "operationName": "database",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "id": <DATABASE_ID>
  },
  "query": "query database($secretKey: String!, $id: ID!) {\n  database(secretKey: $secretKey, id: $id) {\n    _id\n    name\n    fields {\n      _id\n      name\n      type\n      primary\n      required\n      restrict\n      database\n    }\n  }\n}\n"
}
Example result
Returns the specified database. The database has an _id, name, and an array of fields. See the meaning of each field's properties here, and more information about field types.
Success
Error
{
  "data": {
    "database": {
      "_id": "Ccx2Fzva28RshjFpr",
      "name": "Orders",
      "fields": [
        {
          "_id": "Nb6uqwKdxh3BFX3De",
          "name": "ID",
          "type": "string",
          "primary": true,
          "required": true,
          "restrict": null,
          "database": null
        },
        {
          "_id": "RyM8SyrYrceRsrLJd",
          "name": "Client",
          "type": "ref",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": "uJL9XrebupC7eWvM3"
        },
        {
          "_id": "g5WPqJugq9o3fQPXH",
          "name": "Products",
          "type": "refMultiple",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": "LkfjQ9pq9f48K2vD8"
        },
        {
          "_id": "Jex4vcwa9HZ4GipXg",
          "name": "Photo",
          "type": "attachment",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": null
        },
        {
          "_id": "pnR2Mnqfjdj9vQG8e",
          "name": "Amount",
          "type": "currency",
          "primary": null,
          "required": true,
          "restrict": null,
          "database": null
        },
        {
          "_id": "2ybyZfu6H6KNfGbBt",
          "name": "Location",
          "type": "enum",
          "primary": null,
          "required": null,
          "restrict": [
            "Houston",
            "Atlanta",
            "Las Vegas",
            "Minneapolis",
            "New York"
          ],
          "database": null
        },
        {
          "_id": "q6iD8LCHSjhJusLSd",
          "name": "Date",
          "type": "date",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": null
        },
        {
          "_id": "JYszfW2jpLeTck8mW",
          "name": "Department",
          "type": "enumTags",
          "primary": null,
          "required": null,
          "restrict": [
            "Electronics",
            "Cosmetics",
            "Cookware",
            "Hardware",
            "Home"
          ],
          "database": null
        },
        {
          "_id": "hMQKzyxRBxK4sGN5M",
          "name": "Quantity",
          "type": "number",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": null
        }
      ],
      "filters": [],
      "templates": []
    }
  }
}
{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}
data
Gets data from a database.
You can get the database ID from the URL address bar while logged into Pitchly. When inside the account, the URL will follow the pattern:
https://v2.pitchly.net/t/<ORGANIZATION_ID>/d/<DATABASE_ID>/a/<APP_ID>
GraphQL
REST
query data($secretKey: String!, $databaseId: ID!, $filter: JSON, $sort: JSON, $limit: Int, $skip: Int, $in: [String], $fields: [String], $removedAt: Boolean, $search: String) {
  data(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, sort: $sort, limit: $limit, skip: $skip, in: $in, fields: $fields, removedAt: $removedAt, $search: String) {
    _id
    cols {
      fieldId
      value
      rows {
        _id
        cols {
          fieldId
          value
        }
      }      
    }
  }
}
Variables:
{
  "secretKey": <SECRET_KEY>,
  "databaseId": <DATABASE_ID>
}
In the Body of the request:
{
  "operationName": "data",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "databaseId": <DATABASE_ID>
  },
  "query": "query data($secretKey: String!, $databaseId: ID!, $filter: JSON, $sort: JSON, $limit: Int, $skip: Int, $in: [String], $fields: [String], $removedAt: Boolean, $search: String) {\n  data(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, sort: $sort, limit: $limit, skip: $skip, in: $in, fields: $fields, removedAt: $removedAt, search: $search) {\n    _id\n    cols {\n      fieldId\n      value\n    }\n  }\n}\n"
}
Example result
Returns an array of rows. Each row has an _id and cols for each column/field of data in the row. The data in each column can differ based on field type, but generally those variations will take place within the value object. When value is an object, it is guaranteed to at least always have a val property.
When a value is empty, value may either be null (indicates "not set"), or value may be an object where val itself is null (indicates "intentionally empty"). Read more about empty values.
You can also find all of the expected value object formats for each data type here.
Success
Error
{
  "data": {
    "data": [
      {
        "_id": "a5hzm9xChLDKrxneG",
        "cols": [
          {
            "fieldId": "Nb6uqwKdxh3BFX3De",
            "value": {
              "val": "order-2883"
            }
          },
          {
            "fieldId": "RyM8SyrYrceRsrLJd",
            "value": {
              "val": "aobSkpbmx5mrpKc8Y",
              "_label": "Sam Arthur"
            }
          },
          {
            "fieldId": "g5WPqJugq9o3fQPXH",
            "value": {
              "val": [
                "cj659qeYY9LuXAJNB",
                "fHLNMGYyj46cRbFyg",
                "xRfACYg7QmBvTykqW"
              ],
              "_label": [
                "Ceramic Bowl",
                "Coffee Maker",
                "Coffee Mug"
              ]
            }
          },
          {
            "fieldId": "Jex4vcwa9HZ4GipXg",
            "value": {
              "val": "https://ply-files.s3.amazonaws.com/T2qgNJQBz6EZgxGc3i/d1a4189e-9389-4aaf-a62a-c475c278b77c.jpeg",
              "type": "image/jpeg",
              "size": 28813
            }
          },
          {
            "fieldId": "pnR2Mnqfjdj9vQG8e",
            "value": {
              "val": 57,
              "currency": "USD"
            }
          },
          {
            "fieldId": "2ybyZfu6H6KNfGbBt",
            "value": {
              "val": "New York"
            }
          },
          {
            "fieldId": "q6iD8LCHSjhJusLSd",
            "value": {
              "val": "2019-06-27T00:00:00.000Z"
            }
          },
          {
            "fieldId": "JYszfW2jpLeTck8mW",
            "value": {
              "val": [
                "Cookware",
                "Home"
              ]
            }
          },
          {
            "fieldId": "hMQKzyxRBxK4sGN5M",
            "value": {
              "val": 2
            }
          }
        ]
      }
    ]
  }
}
{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}
Advanced data queries
The sample query above may work fine for very simple queries, but the data endpoint allows us to pass many more options to query very specific information. Below is a list of optional variables the data endpoint additionally accepts, beside a secretKey and databaseId.
Variable
Type
Description
filter
object
Narrows rows by specifying filter criteria
sort
object
Sorts rows in ascending or descending order by field ID, in the order specified
limit
number
The maximum number of rows to return (default = 100)
skip
number
Skips the first N number of rows in the result set (useful for pagination)
in
array
Limit resulting rows to the row IDs specified in this array
fields
array
Only return the fields in each row specified in this array of field IDs
removedAt
boolean
Only returns rows in the trash
search
string
Specifies a global search string where , is interpreted as and and | is interpreted as an or
Examples
filter
sort
in
fields
search
Example 1 (simple)
Get rows where ID equals "order-2883"
{
  fields: {
    "Nb6uqwKdxh3BFX3De": { // ID field
      filters: [
        {
          by: "is",
          value: "order-2883"
        }
      ],
      conjunction: "and"
    }
  },
  conjunction: "and"
}
Example 2 (relational)
Get rows in Orders where the Client's Name either contains "Michael" or "John". Note that the Client is a reference to the "Clients" database, which contains a field called "Name". Pitchly is unique in that you can filter data relationally, not unlike a traditional SQL database. The below pattern is also recursive, so you can filter across more than one table or relationship.
{
  fields: {
    "RyM8SyrYrceRsrLJd": { // Client field in "Orders"
      fields: {
        "xEfek48BRpD3kAksh": { // Name field in "Clients"
          filters: [
            {
              by: "contains",
              value: "Michael"
            },
            {
              by: "contains",
              value: "John"
            }
          ],
          conjunction: "or"
        }
      },
      conjunction: "and",
      include: {
        // Switch these to true to include rows where the Client
        // field itself is either empty, not set, or has any value.
        // This is different from filtering by a specific field in
        // the "Clients" database.
        "is-empty": false,
        "is-not-set": false,
        "has-any-value": false
      }
    }
  },
  conjunction: "and"
}
Since different field types accept different filter parameters, here is a list of all the possible filter parameters for each field type. They can be used within the filters array.
Conjunctions can be used to perform AND or OR operations at each step of a filter. Different logical conjunctions can be used on a per-field level and on a per-filter level. Each field can contain multiple filters to perform different logic within the same field.
Sort by Order Amount in descending order, and then by ID in ascending order, yielding the highest priced orders first, and for orders of the same amount, sorted by the order ID alphabetically.
{ "pnR2Mnqfjdj9vQG8e": -1, "Nb6uqwKdxh3BFX3De": 1 }
1 = ascending; -1 = descending; order of fields determines sort priority
Only return at most the two rows identified by the following row IDs.
["a5hzm9xChLDKrxneG", "Q42ggoNpiWzhJ9GDZ"]
This can be used in combination with filter, limit, etc. to further isolate the desired rows. If a row is specified using in but it doesn't pass a given filter, or it exceeds the max number of rows in the result set (specified by limit), the row will be excluded from the result.
If one of the row IDs does not exist in the database, an error will not be thrown. The result will not include any rows that are not actually in the database.
For each row in Orders, only return the ID and Client fields.
["Nb6uqwKdxh3BFX3De", "RyM8SyrYrceRsrLJd"]
{ "search": "deal|money" }
No more than 100 rows can be returned at a time. To return more, you should use skip and limit to paginate through results.
By default, all data in the database will be returned (up to the first 100 rows) in the default sort order, which is currently the time the row was created in descending order (most recent first).
​
content
Gets images and PPT assets for each row in a database.
You can get the database ID from the URL address bar while logged into Pitchly. When inside the account, the URL will follow the pattern:
https://v2.pitchly.net/t/<ORGANIZATION_ID>/d/<DATABASE_ID>/a/<APP_ID>
GraphQL
REST
query content($databaseId: ID!, $secretKey: String!, $filter: JSON, $limit: Int, $skip: Int, $in: [String], $sort: JSON, $search: String, $templatePaths: [String]) {
  content (databaseId: $databaseId, secretKey: $secretKey, filter: $filter, limit: $limit, skip: $skip, in: $in, sort: $sort, search: $search, templatePaths: $templatePaths) {
    _id
    templates {
      name
      path
      pptxUrl
      imageUrl  
      default          
    }
  }
}
Variables:
{
  "secretKey": <SECRET_KEY>,
  "databaseId": <DATABASE_ID>
}
In the Body of the request:
{
  "operationName": "content",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "databaseId": <DATABASE_ID>
  },
  "query": "query content($databaseId: ID!, $secretKey: String!, $filter: JSON, $limit: Int, $skip: Int, $in: [String], $sort: JSON, $search: String, $templatePaths: [String]) {\n  content(\n    databaseId: $databaseId\n    secretKey: $secretKey\n    filter: $filter\n    limit: $limit\n    skip: $skip\n    in: $in\n    sort: $sort\n    search: $search\n    templatePaths: $templatePaths\n  ) {\n    _id\n    templates {\n      name\n      path\n      pptxUrl\n      imageUrl\n      default\n    }\n  }\n}\n"
}
Example result
Returns an array of rows. Each row has an _id and an array oftemplates for each row of data.
Success
Error
{
    "data": {
        "content": [
            {
                "_id": "6MMrY9Du9ShrFYwhb",
                "templates": [
                    {
                        "name": "test",
                        "path": "Folder 1 > Folder 1-1 > Folder 1-1 > Folder 1-1-2 > test",
                        "pptxUrl": "https://tombstones.pitchly.net/content/ppt?token=wmCYMQLs0cs_bx01rl2UkUrYTkG1R10y7WdmqYSUHOdR-K0PcpPJG9ZHPuhpkERlw89NH9Jfqob2Au82dJwC4eknDD_sc4znPNvw3_0Y-UqNCt2cFX65Crugp0vGbex5ICVXLvdgsAGK11vL7optqPK6HOtgb54NGcUoVz2iBJGQ9AlotyawaXMji2xylVh1m4OPgxsdXVzOE4eCnXdXUmNS5kEKa-gp7qSl5bqMHWM",
                        "imageUrl": "https://v2.pitchly.net/content/wmCYMQLs0cs_bx01rl2UkUrYTkG1R10y7WdmqYSUHOdR-K0PcpPJG9ZHPuhpkERlw89NH9Jfqob2Au82dJwC4eknDD_sc4znPNvw3_0Y-UqNCt2cFX65Crugp0vGbex5ICVXLvdgsAGK11vL7optqPK6HOtgb54NGcUoVz2iBJGQ9AlotyawaXMji2xylVh1m4OPgxsdXVzOE4eCnXdXUmNS5kEKa-gp7qSl5bqMHWM",
                        "default": false
                    },
                    {
                        "name": "really nested",
                        "path": "Folder 1 > Folder 1-1 > Folder 1-1 > really nested",
                        "pptxUrl": "https://tombstones.pitchly.net/content/ppt?token=wmCYMQLs0cs_bx01rl2UkUrYTkG1R10y7WdmqYSUHOdR-K0PcpPJG9ZHPuhpkERlw89NH9Jfqob2Au82dJwC4eknDD_sc4znPNvw3_0Y-UqNCt2cFX65Crugp0vGbex5ICVXLvdgsAGK11vL7optqPK6HOtgb54NGcUoVz2iBJGQ9AlotyawaXMji2xylVh1m4OPgxsdXVzOE4eCnXdXUmNS5kEKa-gp7qSl5bqMHWM",
                        "imageUrl": "https://v2.pitchly.net/content/wmCYMQLs0cs_bx01rl2UkUrYTkG1R10y7WdmqYSUHOdR-K0PcpPJG9ZHPuhpkERlw89NH9Jfqob2Au82dJwC4eknDD_sc4znPNvw3_0Y-UqNCt2cFX65Crugp0vGbex5ICVXLvdgsAGK11vL7optqPK6HOtgb54NGcUoVz2iBJGQ9AlotyawaXMji2xylVh1m4OPgxsdXVzOE4eCnXdXUmNS5kEKa-gp7qSl5bqMHWM",
                        "default": false
                    }
                ]
            }
        ]
    }
}
{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}
Advanced content queries
The sample query above may work fine for very simple queries, but the content endpoint allows us to pass many more options to query very specific information. Below is a list of optional variables the content endpoint additionally accepts, beside a secretKey and databaseId.
Variable
Type
Description
filter
object
Narrows rows by specifying filter criteria
sort
object
Sorts rows in ascending or descending order by field ID, in the order specified
limit
number
The maximum number of rows to return (default = 100)
skip
number
Skips the first N number of rows in the result set (useful for pagination)
in
array
Limit resulting rows to the row IDs specified in this array
search
string
Specifies a global search string where , is interpreted as and and | is interpreted as an or
templatePaths
array of string
Specifies one or more templatePaths as returned by the database or databases endpoints.  The returned rows will exactly match only these paths. Omitting this variable or supplying an empty array will return each row with all available templates.
Examples
filter
sort
in
search
Example 1 (simple)
Get rows where ID equals "order-2883"
{
  fields: {
    "Nb6uqwKdxh3BFX3De": { // ID field
      filters: [
        {
          by: "is",
          value: "order-2883"
        }
      ],
      conjunction: "and"
    }
  },
  
Endpoint	Description
databases	Get all the databases that belong to an organization.
database	Get information about a specific database.
data	Get data rows from a database.
dataCount	Get the number of rows in a result.
content	Get images and PPT asset for selected rows.
Variable	Type	Description
filter	object	Narrows rows by specifying filter criteria
sort	object	Sorts rows in ascending or descending order by field ID, in the order specified
limit	number	The maximum number of rows to return (default = 100)
skip	number	Skips the first N number of rows in the result set (useful for pagination)
in	array	Limit resulting rows to the row IDs specified in this array
fields	array	Only return the fields in each row specified in this array of field IDs
removedAt	boolean	Only returns rows in the trash
search	string	Specifies a global search string where , is interpreted as and and \| is interpreted as an or