Queries
How to query content with filters, sorting and pagination.

Query Options

Squidex has a query engine that allows different query languages. So far the following query languages are available:
  1. 1.
    OData queries that is the first system that has been implemented using an existing solution that was easy to adapt, they are easier to write in URLs.
  2. 2.
    JSON queries are newer and are mainly used the UI, because they are faster and easier to parse. It is recommended to use JSON queries in your client.
Both query languages support the same features:
  1. 1.
    Filters with complex comparison operators, conjunctions and negations.
  2. 2.
    Full text search.
  3. 3.
    Sorting by one or multiple fields.
  4. 4.
    Skipping items and restricting the size of the result set for pagination.

OData Queries

OData is an open protocol which allows the creation and consumption of queryable and inoperable APIs in a simple and standardized way. It was designed and developed by Microsoft and provides ready to use solutions. We have decided to use the Query syntax because we wanted to leverage an existing system and parser and found it easy to adapt to our needs.
The queries are provided over the URL and have a special syntax. OData query options start with a dollar character, e.g. $filter.
An example:
https://.../api/content/geodata/cities?$top=30&$skip=10&$search=Munich
Even though we use OData, we do not support the full capabilities and there are no plans to do so. Some features like select, expand or formatting can be better handled with GraphQL.
The full OData convention can be read at:
URI Conventions (OData Version 2.0) · OData - the Best Way to REST

Json Queries

JSON queries will be passed in as URL encoded JSON objects with the q query parameter. They are much harder to read for humans but easier and faster to parse. It has been introduced when a new query editor was implemented for the Management UI.
An example
https://.../api/content/geodata/cities?q=%7B%22fullText%22%3A%22website%22%2C%22take%22%3A10%2C%22sort%22%3A%5B%5D%2C%22filter%22%3A%7B%22and%22%3A%5B%5D%7D%7
As you can see it is horrible to read, therefore we will just show normal JSON examples from now on.

Content structure

We demonstrate the API concepts based on the following example:
Lets assume you have an app geodata with two configured languages: German (de) and English (en).
We also have a schema cities with these fields:
Name
Type
Localizable
Description
name
String
Yes
The name of the city.
population
Number
No
The number of people living in the city.
foundation-year
Number
No
The foundation year.
districts
References
No
References to district content items.
tags
Tags
No
Search tags.
isCapital
Boolean
No
Indicates whether the city is a capital
location
Geolocation
No
The location of the city.
Then your content has the following structure in the API:
{
"id": "01",
"created": "2017-02-25T19:56:35Z",
"createdBy": "...",
"lastModified": "2017-02-25T19:56:35Z",
"lastModifiedBy": "...",
"data": {
"name": {
"de": "München",
"en": "Munich"
},
"population": {
"iv": 1400000
},
"foundation-year": {
"iv": 1200
},
"districts": {
"iv": [
"5921b6f7-9584-49ef-b112-4b830cd0b87a"
]
},
"tags": {
"iv": ["Bavaria", "Beer"]
},
"isCapital": {
"iv": true
},
"location": {
"iv": {
"longitude": 11.576124
"latitude": 48.137154
}
}
}
}
Please note that there is one object for each field, because each field has a partitioning. It defines how the field is structured. The most simple partitioning is the invariant partition, which only allows a single key iv. If the field is localizable we use the languages codes from the languages that you defined in your app settings as keys.
Read more about localization:

How to identity fields

To identify a field of our content item we use the full path to this field, separated by hashes, for example
  • id
  • createdBy
  • data/name/en
  • data/name/iv
  • data/population/iv

Special cases

Dot Notation in JSON queries

When you use JSON queries, you can also use the dot-notation to have a syntax that is closer to Javascript and other programming languages. It is recommended to use this notation. For example:
  • data.population.iv

OData Restrictions

In OData dash characters (-) are not allowed. Therefore you have to replace them with underscore in your queries. To identify the foundation-year field we would use
  • data/foundation_year/ivin OData
  • data.foundation-year.ivin JSON queries.

Query Features

Limiting the number of results

The top / takequery option requests the number of items in the queried collection to be included in the result. The default value is 20 and the maximum allowed value is 200.
OData
JSON
https://.../api/content/geodata/cities?$top=30
{
"take": 30
}
Because of a stupid error the parameter is called top in OData and take in JSON.

Skipping items in the result set

The skip query option requests the number of items in the queried collection that are to be skipped and not included in the result. Use it together with top / take to read the all your data page by page.
OData
JSON
https://.../api/content/geodata/cities?$skip=20
{
"skip": 20
}
or combined with top / take
OData
JSON
https://.../api/content/geodata/cities?$skip=20&$top=30
{
"skip": 20,
"take": 30
}

Full text searches

The search query option allows clients to request entities matching a free-text search expression. We add the data of all fields for all keys to a single field in the database and use this combined field to implement the full text search.
OData
JSON
https://.../api/content/geodata/cities?$search=Munich
{
"fullText": "Munich"
}
You can either use search or filter but not both.

Filters

The filter system query option allows clients to filter a collection of resources that are addressed by a request URL.
Find the city with the name Munich in English.
OData
JSON
https://.../api/content/geodata/cities?$filter=data/name/en eq Munich
{
"filter": {
"path": "data.name.en",
"op": "eq"
"value": "Munich"
}
}
Find all cities with a population or more than 100000 people
OData
JSON
https://.../api/content/geodata/cities?$filter=data/population/iv gt 100000
{
"filter": {
"path": "data.population.iv",
"op": "gt"
"value": 100000
}
}
For example when you want to filter by the foundation year (foundation).
OData
JSON
https://.../api/content/geodata/cities?$filter=data/foundation_year/iv lt 1000
{
"filter": {
"path": "data.foundation-year.iv",
"op": "lt"
"value": 1000
}
}

Array

If you have fields that have array of values, for example references that are represented as an array of content ids, you can still the equal operator. The API will return a content item if at least one item in the array is equal to the passed in value.
For example when we search by tags.
OData
JSON
https://.../api/content/app/term?$filter=data/tags/iv eq 'Beer'
{
"filter": {
"path": "data.tags.iv",
"op": "eq"
"value": "Beer"
}
}
You can either use search or filter but not both.

More examples

An array (components, array fields, references, assets, strings) cannot be empty:
First Tab
// Some code
Date must match value:
OData
JSON
$filter=created eq 1988-01-19T12:00:00Z
{
"filter": {
"path": "created ",
"op": "eq"
"value": "1988-01-19T12:00:00Z"
}
}
Date must match one of many values:
OData
JSON
$filter=created in ('1988-01-19T12:00:00Z', '2011-01-22T08:00:00Z')
{
"filter": {
"path": "created ",
"op": "in"
"value": [
"1988-01-19T12:00:00Z",
"2011-01-22T08:00:00Z"
}
}
}
Id must match value:
OData
JSON
$filter=id eq B5FE25E3-...
---
$filter=id in (B5FE25E3-..., 311DD333-...)
{
"filter": {
"path": "id",
"op": "eq",
"value": "B5FE25E3-..."
}
}
---
{
"filter": {
"path": "id",
"op": "eq",
"value": [
"B5FE25E3-...",
"311DD333-..."
}
}
}
Name must match string value:
OData
JSON
$filter=data/name/en eq 'Munich'
{
"filter": {
"path": "data.name.en",
"op": "eq",
"value": "Munich"
}
}
Boolean must match value:
OData
JSON
$filter=data/isCapital/iv eq true
{
"filter": {
"path": "data.isCapital.iv",
"op": "eq",
"value": true
}
}
Number must match a value:
OData
JSON
$filter=data/population/iv eq 1000000
{
"filter": {
"path": "data.population.iv",
"op": "eq",
"value": 1000000
}
}
String property should start with, ends with or contain a string:
OData
JSON
$filter=startswith(data/name/en, 'Mun')
$filter=startswith(data/name/en, 'Mun') eq true // Aquivalent
---
$filter=endswith(data/name/en, 'ich')
---
$filter=contains(data/name/en, 'ich')
{
"filter": {
"path": "data.name.en",
"op": "startsWith",
"value": "Mun"
}
}
---
{
"filter": {
"path": "data.name.en",
"op": "endsWith",
"value": "ich"
}
}
---
{
"filter": {
"path": "data.name.en",
"op": "contains",
"value": "ich"
}
}
In OData these operators can also be compared with false. In JSON queries you have to use a not operation to negate your filter expression.
OData
JSON
$filter=contains(data/name/en, 'ich') eq false
---
not contains(data/name/en, 'ich')
{
"filter": {
"not": {
"path": "data.name.en",
"op": "contains",
"value": "ich"
}
}
}
In OData single quotes (') in text values must be replaced with double single quotes.
Geolocation must within radius.
OData
JSON
// Point is defined as POINT(longitude latitude)
geo.distance(data/geolocation/iv, geography'POINT(11.576124 48.137154)') lt 1000
{
"filter": {
"path": {
"data.geolocation.iv",
"op": "lt"
// The radius is defined as Radius(Longitude, Latitude, Meters)
"value": "Radius(11.576124, 48.137154, 1000)"
}
}
}
Different conditions
OData
JSON
$filter=data/population/iv ne 1 // Not equals
---
$filter=data/population/iv eq 1 // Equals
---
$filter=data/population/iv lt 1 // Less than
---
$filter=data/population/iv le 1 // Less or equals than
---
$filter=data/population/iv gt 1 // Greater than
---
$filter=data/population/iv ge 1 // Greater or equals than
{
"filter": {
"path": "data.population.iv",
"op": "ne", // Not equals
"value": 1
}
}
---
{
"filter": {
"path": "data.population.iv",
"op": "ne", // Equals
"value": 1
}
}
---
{
"path": "data.population.iv",
"op": "lt", // Less than
"value": 1
}
---
{
"filter": {
"path": "data.population.iv",
"op": "le", // Less or equals than
"value": 1
}
}
---
{
"filter": {
"path": "data.population.iv",
"op": "gt", // Greater than
"value": 1
}
}
---
{
"filter": {
"path": "data.population.iv",
"op": "ge", // Greater or equals than
"value": 1
}
}
Combine different conditions:
OData
JSON
// AND: Both condition must be true
$filter=data/population/iv eq 1000000 and data/isCapital/iv eq true
// OR: One condition must be true
$filter=data/population/iv eq 1000000 or data/isCapital/iv eq true
{
"filter": {
"and": [{ // AND: Both condition must be true
"path": "data.population.iv",
"op": "eq",
"value": 1000000
}, {
"path": "data.capital.iv",
"op": "eq",
"value": true
}]
}
}
---
{
"filter": {
"or": [{ // OR: One condition must be true
"path": "data.population.iv",
"op": "eq",
"value": 1000000
}, {
"path": "data.capital.iv",
"op": "eq",
"value": true
}]
}
}
Negations
OData
JSON
not endswith(data/name/en, 'ich')
{
"filter": {
"not": {
"path": "data.name.ev",
"op": "endswith",
"value": "ich"
}
}
}

Sorting

The orderby or sorting query option allows clients to request resources in a particular order.
e.g. find the top 20 biggest cities by population:
OData
JSON
https://.../api/content/geodata/cities?$orderby=data/population/iv desc$top=20
{
"sort": [{
"path": "data.population.iv",
"order": "descending"
}],
"take": 20
}
Of course you can also sort by multiple fields.
OData
JSON
https://.../api/content/geodata/cities?$orderby=data/population/iv desc,data/name/iv asc$top=20
{
"sort": [{
"path": "data.population.iv",
"order": "descending"
}, {
"path": "data.name.iv",
"order": "ascending"
}],
"take": 20
}

Published items

By default the content api returns only published content. You can use the X-Unpublished header to also return draft content.

Versioning

The API tracks the version of each content element and provides this information in the ETag content header if you make an update (POST, PUT, PATCH) or if you request a single resource. If you request multiple resources, the version is provided as a field to each entry.
You can use this header for two use cases:
  1. 1.
    When you make an update you get the new version. This information can be used to find out if your change has already been written to the read store when you receive the same resource after your update.
  2. 2.
    When you make an update you can use the If-Match header to pass the expected version to the API. If the version does not match to the version in the database another user or client has changed the same resource. Then the 412 (Precondition Failed) status code is returned. You should provide this information to the user and ask if the user wants to reload the data or if the resource should be overwritten (just do not use the If-Match header for the second request).
Read more about the If-Match header at
If-Match - HTTP | MDN
Last modified 5mo ago