Query

Cloud CMS provides SQL-like, structured query for all of your content. The platform uses MongoDB under the hood to store the JSON for your content. MongoDB offers a powerful, unmatched query engine so that you can execute complex lookups of your content to support both your editorial and runtime application needs.

The platform also offers "composite" query operations which let you layer MongoDB queries on top of Elastic Search DSL searches and traversals around node objects.

Cloud CMS automatically enables query for all of your content by storing it within MongoDB collections. These collections run under the hood and power query for all of your content. You're free to fire any MongoDB-compatible query JSON at the Cloud CMS query API methods and it will be executed for you.

Cloud CMS automatically maintains indexes that keep your content queries high performance and fast. You're also free to apply custom indexes should your volume of content become substantial or your query needs specific enough that you might improve them by taking complete control of your own index definitions.

Within the Cloud CMS user interface, query is available for developers, managers and administrators. From within a project, you can access the "Query" menu option to enter your own MongoDB-compatible queries and test things out.

MongoDB's query syntax is SQL-like and matches SQL in terms of it's power and performance. You'll find it offers all of the familiar richness of SQL with operators like AND, OR, IN, NOT, GREATER THAN and everything else you're familiar with. It also offers regular expression support and a few other niceties.

For a full description of what is possible using MongoDB's query syntax, please see:
https://docs.mongodb.com/manual/tutorial/query-documents/

Per-branch Query

Query collections are maintained at a branch level. If you're working in the master branch, it will have it's own collection which represents the tip view of content within that branch. If you fork another branch, it will have its own collection. These collections are automatically maintained for you as you use Cloud CMS.

Permissions

As with everything Cloud CMS, the query API respects the underlying permissions and authorities that have been granted to the objects that are considered result candidates. Authorities are checked before content is retrieved which means that two people could execute the same query and get different results.

An example - suppose that there are 10 content items with the property "category" equal to "appliances". An administrator (who has super authorities and can do just about anything) might run a query like this:

{
    "category": "appliances"
}

And get back 10 results.

However, user A might only have CONSUMER authorities against 4 of those content items.
When person A performs the query, they would only get back a result set of size 4.

Permissions are baked into Cloud CMS all the way down to the core. If you need to get back the full set of content objects for purposes of synchronization or anything else, make sure that you have sufficient authorities to do so.

Query API

There are several methods available on the Cloud CMS REST API to perform queries. Essentially, every type of object or datastore within Cloud CMS supports queries and this support is available through the REST API.

However, here we'll focus on content within a repository and branch. All of the content query methods require that you identify which repository and which branch you're querying against. In addition, all query operations are generally POST verbs in the HTTP vernacular. GET is also supported (in this case, the JSON payload should be URL encoded as a request parameter with the name query).

POST /repositories/{repositoryId}/branches/{branchId}/nodes/query

The content type should be application/json and the payload of the POST should be the query document as required by MongoDB.

For example, to find all documents where category is appliances, you'd POST:

{
    "category": "appliances"
}

Suppose you wanted to run the same query but only find products which were created by either ian, jarmi or michael. And suppose you wanted to only find products whose price was greater than 10.00 but less than or equal to 15.00.

MongoDB lets you express this kind of thing and it might look

{
    "category": "appliances",
    "_system.created_by": {
        "$in": ["ian", "jarmi", "michael"]
    },
    "price": {
        "$gt": 10.00,
        "$lte": 15.00
    }
}

The results that are returned from a query call are identical in structure to those returned by a search or by any recordset API method in Cloud CMS. As such, they contain full pagination information, allowing you to limit the number of objects that come back and the starting location within the database.

Smaller sets perform faster so be mindful of this with your calls.

As with all Cloud CMS queries, you control pagination via request parameters. These include skip and limit. Pagination also allows you specify sort information to control the result set order.

Limiting Fields in Query Results

To limit the fields that come back in your query, use the _fields subobject. This subobject provides key/value pairs where each key is the name of a field to include.

Suppose we have articles with the following schema:

{
    ...,
    "properties": {
        "title": {
            "type": "string"
        },
        "description": {
            "type": "string"
        },
        "category": {
            "type": "string"
        }
    }
}

We could run a query and hand back only the title and the category like this:

POST /repositories/{repositoryId}/branches/{branchId}/nodes/query
{
    "_system.created_on.ms": {
        "$gt": 1001
    },
    "_fields": {
        "title": 1,
        "category": 1
    }
}

And the results might look like this:

{
    "rows": [
        {
            "title": "My First Article",
            "category": "blue"
        },
        {
            "title": "My Second Article",
            "category": "red"
        },
        {
            "title": "My Third Article",
            "category": "blue"
        }                
    ],
    "size": 3,
    "total_rows": 101,
    "offset": 0
}

With the Query API, you can also pass this information in via a request parameter, like this:

POST /repositories/{repositoryId}/branches/{branchId}/nodes/query?fields={"title":1,"category":1}
{
    "_system.created_on.ms": {
        "$gt": 1001
    }
}

More Information

For more information on pagination, please see our documentation on Pagination.

For details on how to get the most out of the MongoDB Query DSL, check out the MongoDB Query Syntax page.

For information on system metadata, which was used above for the _system.created_by property, take a look at our documentation on System Metadata.