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.

databases

Gets all the databases that belong to an organization.
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
1
query databases($secretKey: String!, $organizationId: ID!) {
2
databases(secretKey: $secretKey, organizationId: $organizationId) {
3
_id
4
name
5
fields {
6
_id
7
name
8
type
9
primary
10
required
11
restrict
12
database
13
}
14
}
15
}
Copied!

Variables:

1
{
2
"secretKey": <SECRET_KEY>,
3
"organizationId": <ORGANIZATION_ID>
4
}
Copied!
In the Body of the request:
1
{
2
"operationName": "databases",
3
"variables": {
4
"secretKey": <SECRET_KEY>,
5
"organizationId": <ORGANIZATION_ID>
6
},
7
"query": "query databases($secretKey: String!, $organizationId: ID!) {\n databases(secretKey: $secretKey, organizationId: $organizationId) {\n _id\n name\n fields {\n _id\n name\n type\n primary\n required\n restrict\n database\n }\n }\n}\n"
8
}
Copied!

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
1
{
2
"data": {
3
"databases": [
4
{
5
"_id": "uJL9XrebupC7eWvM3",
6
"name": "Clients",
7
"fields": [
8
{
9
"_id": "xEfek48BRpD3kAksh",
10
"name": "Name",
11
"type": "string",
12
"primary": true,
13
"required": null,
14
"restrict": null,
15
"database": null
16
}
17
]
18
},
19
{
20
"_id": "LkfjQ9pq9f48K2vD8",
21
"name": "Products",
22
"fields": [
23
{
24
"_id": "ELHqkJAFiyRfzdMsP",
25
"name": "Name",
26
"type": "string",
27
"primary": true,
28
"required": null,
29
"restrict": null,
30
"database": null
31
},
32
{
33
"_id": "MBadxhiHvf78yDpPS",
34
"name": "In Stock",
35
"type": "boolean",
36
"primary": null,
37
"required": null,
38
"restrict": null,
39
"database": null
40
}
41
]
42
},
43
{
44
"_id": "Ccx2Fzva28RshjFpr",
45
"name": "Orders",
46
"fields": [
47
{
48
"_id": "Nb6uqwKdxh3BFX3De",
49
"name": "ID",
50
"type": "string",
51
"primary": true,
52
"required": true,
53
"restrict": null,
54
"database": null
55
},
56
{
57
"_id": "RyM8SyrYrceRsrLJd",
58
"name": "Client",
59
"type": "ref",
60
"primary": null,
61
"required": null,
62
"restrict": null,
63
"database": "uJL9XrebupC7eWvM3"
64
},
65
{
66
"_id": "g5WPqJugq9o3fQPXH",
67
"name": "Products",
68
"type": "refMultiple",
69
"primary": null,
70
"required": null,
71
"restrict": null,
72
"database": "LkfjQ9pq9f48K2vD8"
73
},
74
{
75
"_id": "Jex4vcwa9HZ4GipXg",
76
"name": "Photo",
77
"type": "attachment",
78
"primary": null,
79
"required": null,
80
"restrict": null,
81
"database": null
82
},
83
{
84
"_id": "pnR2Mnqfjdj9vQG8e",
85
"name": "Amount",
86
"type": "currency",
87
"primary": null,
88
"required": true,
89
"restrict": null,
90
"database": null
91
},
92
{
93
"_id": "2ybyZfu6H6KNfGbBt",
94
"name": "Location",
95
"type": "enum",
96
"primary": null,
97
"required": null,
98
"restrict": [
99
"Houston",
100
"Atlanta",
101
"Las Vegas",
102
"Minneapolis",
103
"New York"
104
],
105
"database": null
106
},
107
{
108
"_id": "q6iD8LCHSjhJusLSd",
109
"name": "Date",
110
"type": "date",
111
"primary": null,
112
"required": null,
113
"restrict": null,
114
"database": null
115
},
116
{
117
"_id": "JYszfW2jpLeTck8mW",
118
"name": "Department",
119
"type": "enumTags",
120
"primary": null,
121
"required": null,
122
"restrict": [
123
"Electronics",
124
"Cosmetics",
125
"Cookware",
126
"Hardware",
127
"Home"
128
],
129
"database": null
130
},
131
{
132
"_id": "hMQKzyxRBxK4sGN5M",
133
"name": "Quantity",
134
"type": "number",
135
"primary": null,
136
"required": null,
137
"restrict": null,
138
"database": null
139
}
140
]
141
}
142
]
143
}
144
}
Copied!
1
{
2
"errors": [
3
{
4
"message": "An error has occurred",
5
"name": "GQLError",
6
"time_thrown": "2019-11-03T20:26:12.651Z",
7
"data": {
8
"error": "not-authorized",
9
"reason": "This app does not have permission to perform this action."
10
}
11
}
12
],
13
"data": null
14
}
Copied!

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
1
query database($secretKey: String!, $id: ID!) {
2
database(secretKey: $secretKey, id: $id) {
3
_id
4
name
5
fields {
6
_id
7
name
8
type
9
primary
10
required
11
restrict
12
database
13
}
14
}
15
}
Copied!

Variables:

1
{
2
"secretKey": <SECRET_KEY>,
3
"id": <DATABASE_ID>
4
}
Copied!
In the Body of the request:
1
{
2
"operationName": "database",
3
"variables": {
4
"secretKey": <SECRET_KEY>,
5
"id": <DATABASE_ID>
6
},
7
"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"
8
}
Copied!

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
1
{
2
"data": {
3
"database": {
4
"_id": "Ccx2Fzva28RshjFpr",
5
"name": "Orders",
6
"fields": [
7
{
8
"_id": "Nb6uqwKdxh3BFX3De",
9
"name": "ID",
10
"type": "string",
11
"primary": true,
12
"required": true,
13
"restrict": null,
14
"database": null
15
},
16
{
17
"_id": "RyM8SyrYrceRsrLJd",
18
"name": "Client",
19
"type": "ref",
20
"primary": null,
21
"required": null,
22
"restrict": null,
23
"database": "uJL9XrebupC7eWvM3"
24
},
25
{
26
"_id": "g5WPqJugq9o3fQPXH",
27
"name": "Products",
28
"type": "refMultiple",
29
"primary": null,
30
"required": null,
31
"restrict": null,
32
"database": "LkfjQ9pq9f48K2vD8"
33
},
34
{
35
"_id": "Jex4vcwa9HZ4GipXg",
36
"name": "Photo",
37
"type": "attachment",
38
"primary": null,
39
"required": null,
40
"restrict": null,
41
"database": null
42
},
43
{
44
"_id": "pnR2Mnqfjdj9vQG8e",
45
"name": "Amount",
46
"type": "currency",
47
"primary": null,
48
"required": true,
49
"restrict": null,
50
"database": null
51
},
52
{
53
"_id": "2ybyZfu6H6KNfGbBt",
54
"name": "Location",
55
"type": "enum",
56
"primary": null,
57
"required": null,
58
"restrict": [
59
"Houston",
60
"Atlanta",
61
"Las Vegas",
62
"Minneapolis",
63
"New York"
64
],
65
"database": null
66
},
67
{
68
"_id": "q6iD8LCHSjhJusLSd",
69
"name": "Date",
70
"type": "date",
71
"primary": null,
72
"required": null,
73
"restrict": null,
74
"database": null
75
},
76
{
77
"_id": "JYszfW2jpLeTck8mW",
78
"name": "Department",
79
"type": "enumTags",
80
"primary": null,
81
"required": null,
82
"restrict": [
83
"Electronics",
84
"Cosmetics",
85
"Cookware",
86
"Hardware",
87
"Home"
88
],
89
"database": null
90
},
91
{
92
"_id": "hMQKzyxRBxK4sGN5M",
93
"name": "Quantity",
94
"type": "number",
95
"primary": null,
96
"required": null,
97
"restrict": null,
98
"database": null
99
}
100
]
101
}
102
}
103
}
Copied!
1
{
2
"errors": [
3
{
4
"message": "An error has occurred",
5
"name": "GQLError",
6
"time_thrown": "2019-11-03T20:26:12.651Z",
7
"data": {
8
"error": "not-authorized",
9
"reason": "This app does not have permission to perform this action."
10
}
11
}
12
],
13
"data": null
14
}
Copied!

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
1
query data($secretKey: String!, $databaseId: ID!, $filter: JSON, $sort: JSON, $limit: Int, $skip: Int, $in: [String], $fields: [String]) {
2
data(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, sort: $sort, limit: $limit, skip: $skip, in: $in, fields: $fields) {
3
_id
4
cols {
5
fieldId
6
value
7
}
8
}
9
}
Copied!

Variables:

1
{
2
"secretKey": <SECRET_KEY>,
3
"databaseId": <DATABASE_ID>
4
}
Copied!
In the Body of the request:
1
{
2
"operationName": "data",
3
"variables": {
4
"secretKey": <SECRET_KEY>,
5
"databaseId": <DATABASE_ID>
6
},
7
"query": "query data($secretKey: String!, $databaseId: ID!, $filter: JSON, $sort: JSON, $limit: Int, $skip: Int, $in: [String], $fields: [String]) {\n data(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, sort: $sort, limit: $limit, skip: $skip, in: $in, fields: $fields) {\n _id\n cols {\n fieldId\n value\n }\n }\n}\n"
8
}
Copied!

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
1
{
2
"data": {
3
"data": [
4
{
5
"_id": "a5hzm9xChLDKrxneG",
6
"cols": [
7
{
8
"fieldId": "Nb6uqwKdxh3BFX3De",
9
"value": {
10
"val": "order-2883"
11
}
12
},
13
{
14
"fieldId": "RyM8SyrYrceRsrLJd",
15
"value": {
16
"val": "aobSkpbmx5mrpKc8Y",
17
"_label": "Sam Arthur"
18
}
19
},
20
{
21
"fieldId": "g5WPqJugq9o3fQPXH",
22
"value": {
23
"val": [
24
"cj659qeYY9LuXAJNB",
25
"fHLNMGYyj46cRbFyg",
26
"xRfACYg7QmBvTykqW"
27
],
28
"_label": [
29
"Ceramic Bowl",
30
"Coffee Maker",
31
"Coffee Mug"
32
]
33
}
34
},
35
{
36
"fieldId": "Jex4vcwa9HZ4GipXg",
37
"value": {
38
"val": "https://ply-files.s3.amazonaws.com/T2qgNJQBz6EZgxGc3i/d1a4189e-9389-4aaf-a62a-c475c278b77c.jpeg",
39
"type": "image/jpeg",
40
"size": 28813
41
}
42
},
43
{
44
"fieldId": "pnR2Mnqfjdj9vQG8e",
45
"value": {
46
"val": 57,
47
"currency": "USD"
48
}
49
},
50
{
51
"fieldId": "2ybyZfu6H6KNfGbBt",
52
"value": {
53
"val": "New York"
54
}
55
},
56
{
57
"fieldId": "q6iD8LCHSjhJusLSd",
58
"value": {
59
"val": "2019-06-27T00:00:00.000Z"
60
}
61
},
62
{
63
"fieldId": "JYszfW2jpLeTck8mW",
64
"value": {
65
"val": [
66
"Cookware",
67
"Home"
68
]
69
}
70
},
71
{
72
"fieldId": "hMQKzyxRBxK4sGN5M",
73
"value": {
74
"val": 2
75
}
76
}
77
]
78
}
79
]
80
}
81
}
Copied!
1
{
2
"errors": [
3
{
4
"message": "An error has occurred",
5
"name": "GQLError",
6
"time_thrown": "2019-11-03T20:26:12.651Z",
7
"data": {
8
"error": "not-authorized",
9
"reason": "This app does not have permission to perform this action."
10
}
11
}
12
],
13
"data": null
14
}
Copied!

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

Examples

filter
sort
in
fields

Example 1 (simple)

Get rows where ID equals "order-2883"
1
{
2
fields: {
3
"Nb6uqwKdxh3BFX3De": { // ID field
4
filters: [
5
{
6
by: "is",
7
value: "order-2883"
8
}
9
],
10
conjunction: "and"
11
}
12
},
13
conjunction: "and"
14
}
Copied!

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.
1
{
2
fields: {
3
"RyM8SyrYrceRsrLJd": { // Client field in "Orders"
4
fields: {
5
"xEfek48BRpD3kAksh": { // Name field in "Clients"
6
filters: [
7
{
8
by: "contains",
9
value: "Michael"
10
},
11
{
12
by: "contains",
13
value: "John"
14
}
15
],
16
conjunction: "or"
17
}
18
},
19
conjunction: "and",
20
include: {
21
// Switch these to true to include rows where the Client
22
// field itself is either empty, not set, or has any value.
23
// This is different from filtering by a specific field in
24
// the "Clients" database.
25
"is-empty": false,
26
"is-not-set": false,
27
"has-any-value": false
28
}
29
}
30
},
31
conjunction: "and"
32
}
Copied!
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.
1
{ "pnR2Mnqfjdj9vQG8e": -1, "Nb6uqwKdxh3BFX3De": 1 }
Copied!
1 = ascending; -1 = descending; order of fields determines sort priority
Only return at most the two rows identified by the following row IDs.
1
["a5hzm9xChLDKrxneG", "Q42ggoNpiWzhJ9GDZ"]
Copied!
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.
1
["Nb6uqwKdxh3BFX3De", "RyM8SyrYrceRsrLJd"]
Copied!
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).

dataCount

Gets the total number of rows in a database, optionally filtered.
The optional filter and in parameters function the same as they do with the data endpoint.
GraphQL
REST
1
query dataCount($secretKey: String!, $databaseId: ID!, $filter: JSON, $in: [String]) {
2
dataCount(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, in: $in) {
3
count
4
}
5
}
Copied!

Variables:

1
{
2
"secretKey": <SECRET_KEY>,
3
"databaseId": <DATABASE_ID>
4
}
Copied!
In the Body of the request:
1
{
2
"operationName": "dataCount",
3
"variables": {
4
"secretKey": <SECRET_KEY>,
5
"databaseId": <DATABASE_ID>
6
},
7
"query": "query dataCount($secretKey: String!, $databaseId: ID!, $filter: JSON, $in: [String]) {\n dataCount(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, in: $in) {\n count\n }\n}\n"
8
}
Copied!

Example result

Returns the number of rows in the database, or if filter or in are specified, the number of rows in the result, not subject to a limit.
Success
Error
1
{
2
"data": {
3
"dataCount": {
4
"count": 1011
5
}
6
}
7
}
Copied!
1
{
2
"errors": [
3
{
4
"message": "An error has occurred",
5
"name": "GQLError",
6
"time_thrown": "2019-11-03T20:26:12.651Z",
7
"data": {
8
"error": "not-authorized",
9
"reason": "This app does not have permission to perform this action."
10
}
11
}
12
],
13
"data": null
14
}
Copied!

Reference

Field properties

When getting information about the fields in a database using either the database or databases endpoints, the following properties will be returned for each field. A description of each property can be found below.
Field property
Return type
Description
_id
string
The ID of the field
name
string
The name of the field
type
string
The data type of the field. Can be one of these types.
primary
boolean | null
True if this field is the primary field in the database
required
boolean | null
True if this field is required
restrict
array | null
If field type is enum or enumTags, an array of all possible values
database
string | null
If field type is ref or refMultiple, the ID of the database this field refers to

Data types

Each field in a database can be one of the following types. Note that the programmatic name is different than the user-facing name for each type. The programmatic name is used throughout Pitchly's APIs, while the user-facing name is what the user sees through Pitchly's interface. Examples are representative of the value object returned for each field in each row when getting data via the data endpoint.
Field type
User-facing name
Description & example
string
Single-line text
Short single-line text
{ val: "foo" }
textBlock
Multi-line text
Long multi-line text
{ val: "foo\nbar" }
number
Number
Number w/ possible decimal
{ val: 12.5 }
boolean
Yes/No
Binary true/false value
{ val: false }
date
Date
Date in ISO 8601 Extended format (with zeroed UTC time)
{ val: "2020-01-23T00:00:00.000Z" }
currency
Currency
Currency amount & 3-char currency code (see list)
{ val: 50, currency: "USD" }
attachment
Attachment
File attachment of any type (size given in bytes)
{ val: "<URL>", size: 78358, type: "image/jpeg" }
enum
Dropdown
A single value selected from a dropdown of predefined values
{ val: "Las Vegas" }
enumTags
Dropdown multiple
Multiple values selected from a dropdown of predefined values
{ val: ["Los Angeles", "Las Vegas", "San Francisco"] }
ref
Reference
Refers to a row from another database, by row ID
{ val: "TG9PmpmHFiWhe7qDE", _label: "Michael" }
refMultiple
Reference multiple
Refers to multiple rows from another database, by row ID
{ val: ["FhG...", "L6Q..."], _label: ["John", "Sandy"] }

Note on reference fields

The row IDs that are saved in reference fields refer to Pitchly's internal and unchangeable row ID for every row. This ID is globally unique to Pitchly, automatically generated, and is not readily exposed to users or defined by users of Pitchly.
Remember that these row IDs are present in the database being referenced by the ref or refMultiple field containing this value, specified by the database property of the ref field.
_label is a convenient display-friendly version of each row being referenced based on the value of the primary field in the referenced row, returned as a string. If no primary field is set in the referenced database, _label will become the row ID itself.

Note on empty values

Empty values can be represented one of two ways:
  • { fieldId: "...", value: null }
  • { fieldId: "...", value: { val: null } }
The first is when the value is considered "not set," which implies the value was empty when inserted into Pitchly and has never been changed. The second is when the value is "intentionally empty," which means it was set to empty intentionally. Once a value is made "intentionally empty," it cannot become "not set" again.
Note that in the second scenario, val will always be null regardless of the data type. If no values are selected in a Dropdown Multiple, for example, null will be returned instead of an empty array. Empty strings will also be automatically converted to null. This way, it is not necessary to know the data type of every field to check whether a value is empty.
Every field type can also potentially be empty, including boolean fields. Required fields may also contain empty values under certain circumstances.

Filter parameters

Depending on the data type of a field, different filter options are available. At a filter's core is an object usually consisting of a by property, and usually a value or a slight varation thereof. Below is a list of possible filters for each data type.

string / textBlock

  • { by: "is", value: "foo" }
  • { by: "is-not", value: "foo" }
  • { by: "starts-with", value: "foo" }
  • { by: "ends-with", value: "foo" }
  • { by: "contains", value: "foo" }
  • { by: "does-not-contain", value: "foo" }
  • { by: "has-any-value" }
  • { by: "is-empty" }
  • { by: "is-not-set" }
Currently, starts-with, ends-with, contains, and does-not-contain are case insensitive, while is and is-not are case sensitive.

number

  • { by: "is", value: 5 }
  • { by: "is-not", value: 5 }
  • { by: "is-more-than", value: 5 }
  • { by: "is-less-than", value: 5 }
  • { by: "is-between", value: { val1: 5, val2: 10 } }
  • { by: "has-any-value" }
  • { by: "is-empty" }
  • { by: "is-not-set" }

boolean

  • { by: "is-true" }
  • { by: "is-false" }
  • { by: "has-any-value" }
  • { by: "is-empty" }
  • { by: "is-not-set" }

date

  • { by: "on", value: "2020-01-23T00:00:00.000Z" }
  • { by: "after", value: "2020-01-23T00:00:00.000Z" }
  • { by: "before", value: "2020-01-23T00:00:00.000Z" }
  • { by: "between", value: { val1: "2020-01-23T00:00:00.000Z", val2: "2020-02-23T00:00:00.000Z" } }
  • { by: "has-any-value" }
  • { by: "is-empty" }
  • { by: "is-not-set" }

currency

  • { by: "is", value: 5, currency: "USD" }
  • { by: "is-not", value: 5, currency: "USD" }
  • { by: "is-more-than", value: 5, currency: "USD" }
  • { by: "is-less-than", value: 5, currency: "USD" }
  • { by: "is-between", value: { val1: 5, val2: 10 }, currency: "USD" }
  • { by: "has-any-value" }
  • { by: "is-empty" }
  • { by: "is-not-set" }
See all currency types

attachment

  • { by: "has-any-value" }
  • { by: "is-empty" }
  • { by: "is-not-set" }

enum / enumTags

  • { values: ["Houston", "Atlanta"], otherValues: ["is-empty", "is-not-set"] }
The above example will return results where Location contains either Houston, Atlanta, the field is empty, or it's not set.
Because Pitchly allows you to filter data relationally across tables, there is no need to filter directly against a ref or refMultiple field. That's why you won't find those here.
Last modified 1yr ago