<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Radosław Miernik on Medium]]></title>
        <description><![CDATA[Stories by Radosław Miernik on Medium]]></description>
        <link>https://medium.com/@radekmie?source=rss-8ace2fb6a661------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*r-HTFae97gcY_q9dop_kww.jpeg</url>
            <title>Stories by Radosław Miernik on Medium</title>
            <link>https://medium.com/@radekmie?source=rss-8ace2fb6a661------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 01:59:31 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@radekmie/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Dynamic aggregations with SparrowQL]]></title>
            <link>https://medium.com/vazco/dynamic-aggregations-with-sparrowql-dfeb133821e7?source=rss-8ace2fb6a661------2</link>
            <guid isPermaLink="false">https://medium.com/p/dfeb133821e7</guid>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[aggregation]]></category>
            <category><![CDATA[declarative-programming]]></category>
            <category><![CDATA[javascript]]></category>
            <dc:creator><![CDATA[Radosław Miernik]]></dc:creator>
            <pubDate>Wed, 03 Oct 2018 13:01:01 GMT</pubDate>
            <atom:updated>2018-10-03T13:01:01.424Z</atom:updated>
            <content:encoded><![CDATA[<p>If you’ve ever worked with MongoDB <a href="https://docs.mongodb.com/manual/core/aggregation-pipeline/index.html">aggregation pipeline</a> before, you may have noticed that it’s a very powerful tool when it comes to querying the DB. The version 3.2 introduced <a href="https://docs.mongodb.com/manual/core/aggregation-pipeline/index.html">$lookup</a> operator, which allows performing an equivalent of SQL left outer joins. Why is it important? Because with it we may write a single query to get all related data!</p><p>I’ve been working with MongoDB aggregations for over 3 years now. During that time, I’ve written <strong>a lot</strong> of pipelines, many of which include a $lookup stage to reduce the overhead of joins — it performs better than a series of .finds and also stays consistent as the DB may change between .finds.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/1*GI34qDylJUW3tWB2ff6Uzw.png" /><figcaption>SparrowQL Logo</figcaption></figure><p>That’s the origin of <a href="https://github.com/vazco/sparrowql">SparrowQL</a>. It’s just a little pain in the neck to do such common jobs and thus it’d be nice to get rid of it. The idea occurred to me at the beginning of 2018. The initial version was developed soon afterwards and it’s already being used in production for quite <em>some</em> time now. Let’s have a quick look at what we got here.</p><p>First, meet our DB model. Here’s a graph visualization:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/572/1*4qOO0vUM-qbphhBSx4fc8g.png" /><figcaption>Database model graph</figcaption></figure><p>And here’s a textual representation:</p><blockquote>Blogs contain Posts (<em>blogId</em>)<br>Users own Blogs (<em>ownerId</em>)<br>Users write Posts (<em>authorId</em>)</blockquote><p>Now, let’s write it as SparrowQL relations:</p><pre>const relations = [<br>  {to: &#39;Blogs&#39;, from: &#39;Posts&#39;, foreign: &#39;_id&#39;, local: &#39;blogId&#39;},<br>  {to: &#39;Users&#39;, from: &#39;Blogs&#39;, foreign: &#39;_id&#39;, local: &#39;ownerId&#39;},<br>  {to: &#39;Users&#39;, from: &#39;Posts&#39;, foreign: &#39;_id&#39;, local: &#39;authorId&#39;}<br>];</pre><p>Easy, right? Next, fill our DB with some fixtures:</p><pre>const blogs = [<br>  {_id: 0, ownerId: 3, topic: &#39;Best blog!&#39;}<br>];</pre><pre>const posts = [<br>  {_id: 0, authorId: 0, blogId: 0, title: &#39;Being fast for dummies&#39;},<br>  {_id: 1, authorId: 0, blogId: 0, title: &#39;Being declarative&#39;},<br>  {_id: 2, authorId: 1, blogId: 0, title: &#39;Amazing!&#39;},<br>  {_id: 3, authorId: 2, blogId: 0, title: &#39;A sparrow, really?&#39;}<br>];</pre><pre>const users = [<br>    {_id: 0, name: &#39;SparrowQL&#39;, about: &#39;Declarative aggregations&#39;},<br>    {_id: 1, name: &#39;Random #1&#39;, about: &#39;Node.js developer&#39;},<br>    {_id: 2, name: &#39;Random #2&#39;, about: &#39;Bird lover&#39;},<br>    {_id: 3, name: &#39;BlogOwner&#39;, about: &#39;Owner of the blog&#39;}<br>];</pre><pre>await Blogs.insertMany(blogs);<br>await Posts.insertMany(posts);<br>await Users.insertMany(users);</pre><p>Query time! Run a simple query:</p><pre>const {build} = require(&#39;sparrowql&#39;);</pre><pre>const projection = {<br>  blogTopic: &#39;Blogs.topic&#39;,<br>  postTitle: &#39;Posts.title&#39;<br>};</pre><pre>const start = &#39;Posts&#39;;<br>const pipeline = build({projection, relations, start});<br>const results = Posts.aggregate(pipeline).toArray();</pre><pre>// There is also the _id field, but I&#39;ll skip it everywhere.<br>[ { blogTopic: &#39;Best blog!&#39;, postTitle: &#39;Being fast for dummies&#39; },<br>  { blogTopic: &#39;Best blog!&#39;, postTitle: &#39;Being declarative&#39; },<br>  { blogTopic: &#39;Best blog!&#39;, postTitle: &#39;Amazing!&#39; },<br>  { blogTopic: &#39;Best blog!&#39;, postTitle: &#39;A sparrow, really?&#39; } ]</pre><p>Whoa! What happened? Here’s the pipeline:</p><pre>[ { $lookup:<br>     { as: &#39;SPARROW_JOINED_Blogs&#39;,<br>       foreignField: &#39;_id&#39;,<br>       from: &#39;Posts&#39;,<br>       localField: &#39;blogId&#39; } },<br>  { $unwind:<br>     { path: &#39;$SPARROW_JOINED_Blogs&#39;,<br>       preserveNullAndEmptyArrays: true } },<br>  { $project:<br>     { blogTopic: &#39;$SPARROW_JOINED_Blogs.topic&#39;,<br>       postTitle: &#39;$title&#39; } } ]</pre><p>Okay, that’s cool! But what if we want to get the <em>post blog owner name</em>? Using Users.name won’t work, as it’ll resolve with <em>post author name</em>. In this case, the relations graph forms a <a href="https://en.wikipedia.org/wiki/Directed_acyclic_graph">DAG</a> (directed acyclic graph), yet SparrowQL will work correctly only on <a href="https://en.wikipedia.org/wiki/Tree_(graph_theory)">directed trees</a><a href="https://en.wikipedia.org/wiki/Tree_(graph_theory)).">.</a> In case you are not familiar with these terms, or simply want to know what to do now, here it is: aliases. Let’s define them, along with new relations:</p><pre>const aliases = {<br>  Authors: &#39;Users&#39;,<br>  Owners:  &#39;Users&#39;<br>};</pre><pre>const relations = [<br>  {to: &#39;Blogs&#39;,   from: &#39;Posts&#39;, foreign: &#39;_id&#39;, local: &#39;blogId&#39;},<br>  {to: &#39;Owners&#39;,  from: &#39;Blogs&#39;, foreign: &#39;_id&#39;, local: &#39;ownerId&#39;},<br>  {to: &#39;Authors&#39;, from: &#39;Posts&#39;, foreign: &#39;_id&#39;, local: &#39;authorId&#39;}<br>];</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/806/1*pzes49XDmu9EY1SwUxOiuw.png" /><figcaption>Remodeled database graph</figcaption></figure><p>It’s a directed tree! Then, we can finally implement our query:</p><pre>const projection = {<br>  ownerName: &#39;Owners.name&#39;,<br>  postTitle: &#39;Posts.title&#39;<br>};</pre><pre>const start = &#39;Posts&#39;;<br>const pipeline = build({aliases, projection, relations, start});<br>const results = Posts.aggregate(pipeline).toArray();</pre><pre>[ { ownerName: &#39;BlogOwner&#39;, postTitle: &#39;Being fast for dummies&#39; },<br>  { ownerName: &#39;BlogOwner&#39;, postTitle: &#39;Being declarative&#39; },<br>  { ownerName: &#39;BlogOwner&#39;, postTitle: &#39;Amazing!&#39; },<br>  { ownerName: &#39;BlogOwner&#39;, postTitle: &#39;A sparrow, really?&#39; } ]</pre><p>And here’s a pipeline:</p><pre>[ { $lookup:<br>     { as: &#39;SPARROW_JOINED_Blogs&#39;,<br>       foreignField: &#39;_id&#39;,<br>       from: &#39;Blogs&#39;,<br>       localField: &#39;blogId&#39; } },<br>  { $unwind:<br>     { path: &#39;$SPARROW_JOINED_Blogs&#39;,<br>       preserveNullAndEmptyArrays: true } },<br>  { $lookup:<br>     { as: &#39;SPARROW_JOINED_Owners&#39;,<br>       foreignField: &#39;_id&#39;,<br>       from: &#39;Users&#39;,<br>       localField: &#39;SPARROW_JOINED_Blogs.ownerId&#39; } },<br>  { $unwind:<br>     { path: &#39;$SPARROW_JOINED_Owners&#39;,<br>       preserveNullAndEmptyArrays: true } },<br>  { $project:<br>     { ownerName: &#39;$SPARROW_JOINED_Owners.name&#39;,<br>       postTitle: &#39;$title&#39; } } ]</pre><p>As you can see, SparrowQL has done a good job, and prepared a nice pipeline to fulfill our needs. But what if we don’t need any special logic? Let’s check it with a simple query:</p><pre>const projection = {<br>  postTitle: &#39;Posts.title&#39;<br>};</pre><pre>const start = &#39;Posts&#39;;<br>const pipeline = build({aliases, projection, relations, start});<br>const results = Posts.aggregate(pipeline).toArray();</pre><pre>[ { postTitle: &#39;Being fast for dummies&#39; },<br>  { postTitle: &#39;Being declarative&#39; },<br>  { postTitle: &#39;Amazing!&#39; },<br>  { postTitle: &#39;A sparrow, really?&#39; } ]</pre><p>And a simple pipeline was generated:</p><pre>[ { $project: { postTitle: &#39;$title&#39; } } ]</pre><p>That’s not everything! SparrowQL can do a few more things:</p><ul><li>handle limit and skip, which maps to <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/limit/">$limit</a> and <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/skip/">$skip</a> directly,</li><li>handle query, which maps to <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/match/">$match</a> directly (*),</li><li>handle sort, which maps to <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/sort/">$sort</a> directly (*).</li></ul><p><em>(*) They may result in more than one phase.</em></p><p>All of them are easy enough if you understood at least a part of the above. There’s also `computed`, which is an advanced feature, designed to handle a <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/group/">$group</a> phase.</p><p>Let’s add something to our DB and run a <em>complex</em> query, with limit, skip, query and sort:</p><pre>await Posts.insertMany([<br>  {_id: 4, authorId: 1, blogId: 0, title: &#39;Best!&#39;},<br>  {_id: 5, authorId: 1, blogId: 0, title: &#39;Superb!&#39;}<br>]);</pre><pre>const limit = 1;<br>const projection = {<br>  blogOwnerName: &#39;Owners.name&#39;,<br>  postAuthorName: &#39;Authors.name&#39;,<br>  postTitle: &#39;Posts.title&#39;<br>};</pre><pre>const query = {&#39;Authors.name&#39;: &#39;Random #1&#39;};<br>const skip = 1;<br>const sort = {&#39;Posts.title&#39;: -1};<br>const start = &#39;Posts&#39;;<br>const pipeline = build({<br>  aliases,<br>  limit,<br>  projection,<br>  query,<br>  relations,<br>  skip,<br>  sort,<br>  start<br>});<br>const results = Posts.aggregate(pipeline).toArray();</pre><pre>[ { blogOwnerName: &#39;BlogOwner&#39;,<br>    postAuthorName: &#39;Random #1&#39;,<br>    postTitle: &#39;Best!&#39; } ]</pre><p>Which generates a <em>complex</em> pipeline:</p><pre>[ { $sort: { title: -1 } },<br>  { $lookup:<br>     { as: &#39;SPARROW_JOINED_Authors&#39;,<br>       foreignField: &#39;_id&#39;,<br>       from: &#39;Users&#39;,<br>       localField: &#39;authorId&#39; } },<br>  { $unwind:<br>     { path: &#39;$SPARROW_JOINED_Authors&#39;,<br>       preserveNullAndEmptyArrays: true } },<br>  { $match: { &#39;SPARROW_JOINED_Authors.name&#39;: &#39;Random #1&#39; } },<br>  { $sort: { title: -1 } },<br>  { $skip: 1 },<br>  { $limit: 1 },<br>  { $lookup:<br>     { as: &#39;SPARROW_JOINED_Blogs&#39;,<br>       foreignField: &#39;_id&#39;,<br>       from: &#39;Blogs&#39;,<br>       localField: &#39;blogId&#39; } },<br>  { $unwind:<br>     { path: &#39;$SPARROW_JOINED_Blogs&#39;,<br>       preserveNullAndEmptyArrays: true } },<br>  { $lookup:<br>     { as: &#39;SPARROW_JOINED_Owners&#39;,<br>       foreignField: &#39;_id&#39;,<br>       from: &#39;Users&#39;,<br>       localField: &#39;SPARROW_JOINED_Blogs.ownerId&#39; } },<br>  { $unwind:<br>     { path: &#39;$SPARROW_JOINED_Owners&#39;,<br>       preserveNullAndEmptyArrays: true } },<br>  { $project:<br>     { blogOwnerName: &#39;$SPARROW_JOINED_Owners.name&#39;,<br>       postAuthorName: &#39;$SPARROW_JOINED_Authors.name&#39;,<br>       postTitle: &#39;$title&#39; } } ]</pre><p>That’d be all for starters. Got hooked with the idea? If so, why not try out SparrowQL in your own project? It only takes minutes to get it up and running while potential profits are significant — less time spent on pipelining queries and boost on performance at once. SparrowQL — no reason to miss out on that little bird!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dfeb133821e7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/vazco/dynamic-aggregations-with-sparrowql-dfeb133821e7">Dynamic aggregations with SparrowQL</a> was originally published in <a href="https://medium.com/vazco">Vazco</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>