Skip to main content
100%

GROQ - tricks

✓ Published0🌍 Public
Aabonello
Last edited Apr 13, 2026
Created on Apr 13, 2026

This example demonstrates a collection of GROQ query patterns for retrieving and aggregating data from a Sanity CMS dataset. The code shows how to filter and sort project milestones to find the latest status, use boolean checks to determine project completion, implement pagination for posts, and perform SQL-like grouping by counting related documents. It introduces GROQ's native aggregation functions such as `count()`, `math::sum`, `math::avg`, and `math::max` to compute statistics directly within the query language. The group-by example uses a parent-centric projection with the `references(^._id)` function to achieve relational grouping.

AI-generated description

Similar vizzes

Loading thumbnail…

Gist 6114786

This example implements a private torque-style time-series visualization for geospatial data using PostgreSQL and PostGIS. It defines custom SQL functions that aggregate point features into web-Mercator grid cells, grouping counts by time steps and returning arrays of values and dates for rendering. The code uses `ST_SnapToGrid`, `CDB_XYZ_Resolution`, and `CDB_XYZ_Extent` to tile data at a given zoom level, with two variants: a standard response for instantaneous counts and a cumulative response that includes expiration dates for each cell. A metadata function returns the spatial bounds and time range from the source table, supporting both timestamped and numeric columns.

Aandrewxhill
56% match
Loading thumbnail…

just a bookmark of random psql queries.

This example visualizes spatial relationships between neighborhoods, city blocks, and demographic data in Portland and Laurelhurst through a series of SQL queries. The code shows how geographic joins and intersections are performed using PostGIS functions like `ST_Intersects`, combining building footprints, block polygons, and census block-group data. The queries calculate race-based percentages, such as percent white and percent black, from census fields like `h7x002` and `h7x003`, while filtering for specific places and positive population counts. The gist demonstrates iterative query building for choropleth-style analysis, revealing how raw geographic and tabular data are merged to support subsequent mapping or charting in a visualization tool.

CCaged
55% match
Loading thumbnail…

facets

This example visualizes a faceted breakdown of book metadata, displaying each facet as a distinct element with its value and count. It shows the distribution of terms across the dataset before explaining the mechanism: the XQuery script uses `fetch:text` and `csv:parse` to load and structure the CSV data, then groups books by each specified field path from an external XML file. The `local:get_facet` function dynamically builds facet elements, ordering results by frequency. The visualization relies on the BaseX XQuery processor’s string and sequence functions, not a client-side library, and renders the output as structured XML data.

CCliffordAnderson
53% match
Loading thumbnail…

Gist 530074b6f87de7b26e25

This example organizes a curated list of Elixir libraries into a hierarchical markdown document, grouping tools by categories such as web frameworks, authentication, testing, and database clients. The data is presented as nested bullet points with hyperlinks to each project’s GitHub repository, mirroring the structure of the source gist. The rendering relies on standard Markdown syntax for headings, lists, and links, making the categorization immediately visible without additional JavaScript or visualization libraries.

Aallyraza
53% match
Loading thumbnail…

Gist 740340

This example demonstrates a parallelized aggregate operation that sums the integers from zero to one million using Language-Integrated Query (LINQ). The code uses the `Enumerable.Range` method to generate the data sequence and calls `AsParallel()` to enable parallel execution across multiple threads. The `Aggregate` function then combines each element with an accumulator, applying the addition lambda `(x, y) => x + y` to produce the total. The visualization likely maps the resulting sum or the execution process, though the rendering method is not specified in the provided source code.

RRestuta
53% match
Loading thumbnail…

MongoDB equivalent of an SQL query to get the distinct values of a field in a collection including the count of documents which have each distinct value (distinct with count)

This example demonstrates how to retrieve distinct values of a field in a MongoDB collection along with the document count for each value, mirroring a SQL `GROUP BY` query. It shows two aggregation pipelines using MongoDB’s `aggregate()` method with the `$group` stage to group by the field `_id` and compute a `count` via `$sum:1`, and an optional `$sort` stage to order the results by count descending. The first pipeline returns unsorted counts, while the second sorts them from highest to lowest. No external visualization library is used; the output is raw aggregated data from the database.

Aackuser
53% match
Loading thumbnail…

rundown

This example demonstrates how SQL queries can filter, analyze, and modify geographic data in CartoDB, using Massachusetts census blocks and Boston corner store locations. It shows spatial queries like distance calculations and bounding-box filters, plus joins to compute nearest fire station distances. The code uses the CartoDB SQL API and CartoDB.js library, with Leaflet for map rendering, to apply dynamic SQL filters and create visualizations without altering the underlying dataset.

Aandrewxhill
53% match
Loading thumbnail…

CartoDB SQL tricks: Various things that as a SQL-newbie I had to search for >10 minutes to find answers for.

This example demonstrates a collection of SQL recipes for solving common spatial data problems in CartoDB, ranging from merging neighboring county polygons with ST_Union to creating great circle lines via ST_MakeLine and ST_Segmentize. It shows practical techniques like updating geometries with conditional CASE statements, extracting coordinates with ST_X and ST_Y, stripping HTML tags using regexp_replace, and formatting zip codes with to_char. The code also covers performance strategies for large updates and querying spatial relationships with st_contains. Each snippet targets PostGIS functions within the CartoDB platform, emphasizing data preparation and geometric transformations.

Aandrewbt
53% match