Data Exploration
Edit pageNow that we’ve gotten a glimpse of the basics, let’s try to work on a more realistic dataset. We have prepared a sample of fictitious JSON documents of customer bank account information. Each document has the following form:
{ "accountNumber": 121931, "balance": 221.46, "employer": "Globoil", "name": { "firstName": "Michael", "lastName": "Lee" }, "age": 24, "gender": "male", "contact": { "address": "630 Victor Road", "city": "Leyner", "state": "Indiana", "postcode": "61952", "phone": "+1 (924) 594-3216", "email": "michael.lee@globoil.co.uk" }, "checkin": { "_point": { "_longitude": -95.63079, "_latitude": 31.76212 } }, "favoriteFruit": "lemon", "eyeColor": "blue", "style": { "clothing": { "pants": "khakis", "shirt": "t-shirt" }, "hairstyle": "slick back" }, "personality": "Everyone has an opinion on Michael Lee..."}For the curious, this data was generated using Faker, using the generator.py script, so please ignore the actual values and semantics of the data as these are all randomly generated.
Loading the Sample Dataset
Section titled “Loading the Sample Dataset”After downloading the sample dataset, let’s load it into our node as follows:
RESTORE /bank/Content-Type: application/x-ndjson
@accounts.ndjsonMore information about RESTORE method can be found in the
Backups section.
After having loaded the dataset, you can then use the Info API to get information about the new index:
INFO /bank/Response should be something like:
{ "endpoint": "bank", "doc_count": 1000, "last_id": 1000, "doc_del": 0, "av_length": 200.023, "doc_len_lower": 176, "doc_len_upper": 238, "has_positions": true, "shards": [ "bank/.__1", "bank/.__2", "bank/.__3", "bank/.__4", "bank/.__5" ]}Which means that we just successfully bulk indexed 1000 documents into the bank index.
The Search API
Section titled “The Search API”Now let’s start with some simple searches. There are two basic ways to run searches: one is by sending search parameters through the REST request URI and the other by sending them through the REST request body. The request body method allows you to be more expressive and also to define your searches in a more readable JSON format. We’ll try one example of the request URI method but for the remainder of this guide, we will exclusively be using the request body method.
The REST API for search is accessible using the SEARCH method. This example
returns all documents in the bank index:
SEARCH /bank/?sort=accountNumber&prettyLet’s first dissect the search call. We are searching in the /bank/ index.
Without a q parameter the search matches all documents in the index (the
default; q=* says it explicitly). The sort=accountNumber parameter
indicates to sort the results using the accountNumber field of each document
in an ascending order. The pretty parameter just tells Xapiand to return
pretty-printed JSON results, a similar effect can be achieved by using the
Accept header as in: Accept: application/json; indent: 2.
And the response (partially shown), as a node started with -vvvv returns it:
{ "total": 1000, "count": 10, "hits": [ { "accountNumber": 100123, "balance": 10073.05, "employer": "Affluex", "name": { "firstName": "Margaret", "lastName": "Anderson" }, "age": 24, "gender": "female", "contact": { "address": "756 Strauss Street", "city": "Fairview", "state": "Virgin Islands", "postcode": "06099", "phone": "+1 (919) 400-3616", "email": "margaret.anderson@affluex.net" }, "checkin": { "_point": { "_longitude": -122.39168, "_latitude": 40.58654 } }, "favoriteFruit": "lemon", "eyeColor": "brown", "style": { "clothing": { "pants": "mini-skirt", "shirt": "jersey", "footwear": "sneakers" } }, "personality": "There's a lot to say about Margaret...", "_id": 233, "_version": 1, "#docid": 233, "#shard": 2, "#rank": 0, "#weight": 0.0, "#percent": 100 }, ... ], "took": "39.889ms"}As for the response, we see the following parts:
total- Number of estimated documents that match the query.count- Total number of returned hits.hits- Search results.aggregations- The results of the aggregations of the request, when it has any.took- Time it took to execute the query: seconds as a number, or a humanized string such as39.889mswhenhumanis on (--human,-vvvv).
The fields that start with # in each hit (#docid, #shard, #rank,
#weight and #percent) are comments: they appear only when comments is on
(--comments, -vvvv, or ?comments on the request). A hit always carries its
document’s _id and _version.
SEARCH /bank/?limit=1&comments=trueThe COUNT method takes the same parameters and body as SEARCH, and returns
only the total:
COUNT /bank/
{ "_query": { "gender": "female" }}Introducing the Query Language
Section titled “Introducing the Query Language”Xapiand provides a JSON-style domain-specific language that you can use to execute queries. This is referred to as the Query DSL. The query language is quite comprehensive and can be intimidating at first glance but the best way to actually learn it is to start with a few basic examples.
Going back to our last example, we executed a query to retrieve all documents
with no q. Here is the same exact search using the alternative request body
method:
SEARCH /bank/
{ "_query": "*", "_sort": "accountNumber"}The difference here is that instead of passing sort in the URI, we send a
JSON-style query request body with the SEARCH method.
Dissecting the above, the _query key holds the query definition and _sort
the order of the results. The * query is simply a search for all documents in
the specified index (the default).
In addition to the query parameter, we also can pass other parameters to
influence the search results. In the example in the section above we passed in
sort, here we pass in limit:
SEARCH /bank/
{ "_query": "*", "_limit": 1}Note that if limit is not specified, it defaults to 10.
This example does a match all and returns documents 10 through 19:
SEARCH /bank/
{ "_query": "*", "_offset": 10, "_limit": 10}The offset parameter (0-based) specifies which document index to start from
and the limit parameter specifies how many documents to return starting at the
given offset. This feature is useful when implementing paging of search
results. Note that if offset is not specified, it defaults to 0.
This example does a match all and sorts the results by account balance in
descending order and returns the top 10 (default for limit) documents.
SEARCH /bank/
{ "_query": "*", "_sort": { "balance": { "_order": "desc" } }}To sort by several fields, give a list; each entry names a field and its order
(asc or desc):
SEARCH /bank/
{ "_query": "*", "_sort": [ {"age": "asc"}, {"balance": "desc"} ]}Executing Searches
Section titled “Executing Searches”Now that we have seen a few of the basic search parameters, let’s dig in some
more into the Query DSL. Let’s first take a look at the returned document
fields. By default, the full JSON document is selected and returned as part of
all searches. If we don’t want the entire document returned, we have the ability
to request only a few fields from within it to be returned by selecting them
by using _selector field during the search.
There are two types of selectors (which can be mixed):
- Field Selector
- Drill Selector
Field Selector
Section titled “Field Selector”It takes the form of "{field1,field2}", and it selects only field1 and
field2 to be returned.
This example shows how to return two fields using the Field Selector,
accountNumber and balance, from the search:
SEARCH /bank/
{ "_query": "*", "_selector": "{accountNumber,balance}"}Drill Selector
Section titled “Drill Selector”It takes the form of "field.sub_field.sub_sub_field", and it brings the
innermost field to the top level.
This example shows how to return a list of emails using the Drill Selector from the search:
SEARCH /bank/
{ "_query": "*", "_selector": "contact.email"}Executing Filters
Section titled “Executing Filters”SEARCH /bank/
{ "_limit": 1000, "_query": { "balance": { "_in": { "_range": { "_from": 2000, "_to": 3000 } } } }}Executing Aggregations
Section titled “Executing Aggregations”Aggregations provide the ability to group and extract statistics from your data.
The easiest way to think about aggregations is by roughly equating it to the
SQL GROUP BY and the SQL aggregate functions. In Xapiand, you have the ability
to execute searches returning hits and at the same time return aggregated
results separate from the hits all in one response. This is very powerful and
efficient in the sense that you can run queries and multiple aggregations and
get the results back of both (or either) operations in one shot avoiding network
roundtrips using a concise and simplified API.
To start with, this example groups all the accounts by state, and then returns the count of accounts by state:
SEARCH /bank/
{ "_query": "*", "_limit": 0, "_check_at_least": 1000, "_aggregations": { "group_by_state": { "_values": { "_field": "contact.state", "_keyed": true } } }}In SQL, the above aggregation is similar in concept to:
SELECT state, COUNT(*) FROM bank GROUP BY state;And the response (partially shown):
{ "total": 1000, "count": 0, "aggregations": { "_doc_count": 1000, "group_by_state": { "Texas": { "_doc_count": 15 }, "Pennsylvania": { "_doc_count": 20 }, "Oklahoma": { "_doc_count": 16 }, ... "Nebraska": { "_doc_count": 17 } } }, "hits": [], "took": "14.83ms"}There are many other aggregations capabilities that we won’t go into detail here. The Aggregations Reference Guide is a great starting point if you want to do further experimentation.
Where to go from here
Section titled “Where to go from here”You have now indexed, updated, searched and aggregated documents. From here:
- The Reference Guide describes every request and every query, schema and aggregation in detail.
- The Tutorials walk through complete scenarios, such as spatial search.
- Options lists every setting of the node, and how to stop it.
- Basic Concepts explains documents, indexes, nodes and shards.