Query rows

Queries the records of a form as a rows.

The caller must provide a list of columns to retrieve, providing an arbitrary columnId as the key, and a formula for the value of the column. The columnId provided will be a key in the resulting json objects.

You can provide a filter using a boolean-valued ActivityInfo formula, or a filter set, which is a pair of a formula and a set of values to include. Filter sets are more efficient than formulas when you have a large number of values.

Request

POST https://www.activityinfo.org/resources/query/rows

Request body

application/json

object
formId	string		required
columns	array[object]		required
id	string	The id of the column. This is used to identify the column in the resulting table.	required
formula	string		required
filter	string	A boolean-valued formula to filter the records included in this query.	optional
sort	array[object]		required
field	string	The field on which to sort.	required
dir	string	The direction to sort. Values: ASC DESC	required
filterSets	array[object]	Zero or more set-based filters to apply. Resulting rows must match both the {@link #filter} formula, if provided, and all of the provided filterSets.	required
columnFormula	string		required
values	array[string]		required

Response

Status Code	Error Code	Description
401	AUTHENTICATION_REQUIRED	The request must be authenticated

Example

curl -X POST https://www.activityinfo.org/resources/query/rows \
  -u anything:APITOKEN \
  -H 'Content-type: application/json' \
  --data-binary @- << EOF
{
  "rowSources": [
    {
      "rootFormId": "ck8pu9ha07"
    }
  ],
  "columns": [
    {
      "id": "id",
      "expression": "_id"
    },
    {
      "id": "email",
      "expression": "EMAIL_FIELD_CODE"
    },
    {
      "id": "full_name",
      "expression": "CONCAT(FIRST_NAME, \" \", LAST_NAME)"
    },
    {
      "id": "latitude",
      "expression": "location.latitude"
    },
    {
      "id": "longitude",
      "expression": "location.longitude"
    }
  ],
  "truncateStrings": false,
  "filter": "EMAIL_FIELD_CODE == \"test@example.com\"",
  "filterSets": [
    {
      "columnFormula": "_id",
      "values": [
        "ck34ujs6v8",
        "cv5wyndkqv9yuuq2"
      ]
    }
  ],
  "sort": [
    {
      "field": "EMAIL_FIELD_CODE",
      "dir": "DESC"
    }
  ]
}
EOF

The above command returns JSON structured like this:

[
  {
    "id": "ck34ujs6v8",
    "email": "test@example.com",
    "full_name": "Bob Smith",
    "latitude": 52.0705,
    "longitude": 4.3007
  },
  {
    "id": "cv5wyndkqv9yuuq2",
    "email": "test@example.com",
    "full_name": "Ngozi Adebayo",
    "latitude": 6.465,
    "longitude": 3.406
  }
]

Next item

Recover database