/* Most time-series databases force you to pre-aggregate
or flatten your data before you can query across devices
and dimensions together.
With CrateDB, you join device metadata at query time.
No pre-processing, no ETL, no schema redesign. */
/* Based on device data, this query returns the average
of the battery level for every hour for each device_id */
WITH avg_metrics AS (
SELECT device_id,
DATE_BIN('1 hour'::INTERVAL, time, 0) AS period,
AVG(battery_level) AS avg_battery_level
FROM devices.readings
GROUP BY 1, 2
ORDER BY 1, 2
)
SELECT period,
t.device_id,
manufacturer,
avg_battery_level
FROM avg_metrics t, devices.info i
WHERE t.device_id = i.device_id
AND model = 'mustang'
LIMIT 10;
+---------------+------------+--------------+-------------------+
| period | device_id | manufacturer | avg_battery_level |
+---------------+------------+--------------+-------------------+
| 1480802400000 | demo000001 | iobeam | 49.25757575757576 |
| 1480806000000 | demo000001 | iobeam | 47.375 |
| 1480802400000 | demo000007 | iobeam | 25.53030303030303 |
| 1480806000000 | demo000007 | iobeam | 58.5 |
| 1480802400000 | demo000010 | iobeam | 34.90909090909091 |
| 1480806000000 | demo000010 | iobeam | 32.4 |
| 1480802400000 | demo000016 | iobeam | 36.06060606060606 |
| 1480806000000 | demo000016 | iobeam | 35.45 |
| 1480802400000 | demo000025 | iobeam | 12 |
| 1480806000000 | demo000025 | iobeam | 16.475 |
+---------------+------------+--------------+-------------------+
/* JSON fields are first-class citizens in CrateDB.
You can filter, sort, and project nested document fields using
standard SQL bracket notation.
No unpacking step, no separate document store, no ORM gymnastics. */
/* Return the name and truncated description for the 5 Chicago community
areas with populations over 50,000 people. */
SELECT name,
details['population'] AS population,
concat(left(details['description'], 25), '...') AS description
FROM community_areas
WHERE details['population'] > 50000
ORDER BY details['population'] DESC
LIMIT 5;
+-----------------+------------+------------------------------+
| name | population | description |
+-----------------+------------+------------------------------+
| NEAR NORTH SIDE | 105481 | The Near North Side is th... |
| LAKE VIEW | 103050 | Lakeview, also spelled La... |
| AUSTIN | 96557 | Austin is one of 77 commu... |
| WEST TOWN | 87781 | West Town, northwest of t... |
| BELMONT CRAGIN | 78116 | Belmont Cragin is one of ... |
+-----------------+------------+------------------------------+
/* CrateDB's full-text search is built on Lucene,
the same engine as Elasticsearch — but accessed through SQL.
You get relevance scoring, field weighting, and BM25 ranking without running
a separate search cluster alongside your database. */
SELECT show_id, title, director, country, release_year, rating, _score
FROM "netflix_catalog"
WHERE MATCH(title_director_description_ft, 'title^2 Friday') USING best_fields
AND type='Movie'
ORDER BY _score DESC;
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
| show_id | title | director | country | release_year | rating | _score |
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
| s1674 | Black Friday | Anurag Kashyap | India | 2004 | TV-MA | 5.6455536 |
| s6805 | Friday the 13th | Marcus Nispel | United States | 2009 | R | 3.226806 |
| s1038 | Tuesdays & Fridays | Taranveer Singh | India | 2021 | TV-14 | 3.1089375 |
| s7494 | Monster High: Friday Night Frights | Dustin McKenzie | United States | 2013 | TV-Y7 | 3.0620003 |
| s3226 | Little Singham: Mahabali | Prakash Satam | NULL | 2019 | TV-Y7 | 3.002901 |
| s8233 | The Bye Bye Man | Stacy Title | United States, China | 2017 | PG-13 | 2.9638999 |
| s8225 | The Brawler | Ken Kushner | United States | 2019 | TV-MA | 2.8108454 |
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
/* Vector search runs inside the same SQL engine as your analytics.
No separate vector database, no synchronization overhead, no dual-write pipeline.
One query can combine KNN similarity with filters, time constraints, and aggregations. */
SELECT text, _score
FROM word_embeddings
WHERE knn_match(embedding,[0.3, 0.6, 0.0, 0.9], 2)
ORDER BY _score DESC;
|------------------------|--------|
| text | _score |
|------------------------|--------|
|Discovering galaxies |0.917431|
|Discovering moon |0.909090|
|Exploring the cosmos |0.909090|
|Sending the mission |0.270270|
|------------------------|--------|
/* Geospatial queries — distance, containment, routing —
run in the same distributed SQL engine as your time-series and analytical workloads.
No PostGIS extension to manage, no separate GIS layer. */
/* Using 311 data from the City of Chicago, this query returns 5 open
work orders for locations closest to the Willis Tower. */
SELECT srnumber,
srtype,
locationdetails['streetaddress'] AS address,
distance(
'POINT(-87.636256 41.8786492)'::GEO_POINT,
locationdetails['location']
) / 1000 AS distance_km
FROM three_eleven_calls
WHERE status != 'Completed'
ORDER BY distance_km ASC
LIMIT 5;
+---------------+-----------------------------------------------+--------------------+---------------------+
| srnumber | srtype | address | distance_km |
+---------------+-----------------------------------------------+--------------------+---------------------+
| SR24-00711535 | Cab Feedback | 200 S WACKER DR | 0.09800707616741176 |
| SR24-00694851 | No Building Permit and Construction Violation | 300 W ADAMS ST | 0.1346164665090538 |
| SR24-00651822 | Sign Repair Request - All Other Signs | 111 SW WACKER DR | 0.20355339153863516 |
| SR24-00608464 | Building Violation | 235 W VAN BUREN ST | 0.26374860571526554 |
| SR24-00608655 | Building Violation | 235 W VAN BUREN ST | 0.26374860571526554 |
+---------------+-----------------------------------------------+--------------------+---------------------+