Jinja Loops in SQL

In Jinja, a for loop is used to iterate over a sequence of elements, such as a list or a dictionary. The syntax for a for loop in Jinja is similar to the syntax for a for loop in Python.

There are no while or other types loops in Jinja. Even though a recursion could technically be built using macros.

Jinja loops are especially useful for generating dynamic SQL queries that change based on user input or other variables. For example, a data analyst might use a Jinja loop to generate a SQL query that filters data based on user-specified criteria, such as a specific date range or a specific set of columns to include in the results.

Here is an example of how a for loop would work in Jinja:

{% set values = [ 'Central','South','East','West'] %}
SELECT
{% for value in values %}
	'{{value}}' as {{ value }},
{% endfor %}

Note: The above query returns an error. Lear about Loop Variables below.

In this example, value is a variable that takes on each value in the values one at a time, starting with the first element and ending with the last element. The block of code inside the for loop (in this case, {{ value }}) is executed for each iteration of the loop.

The resulting SQL Query generates four columns. Each with the same value as the name of the column.


Loop-specific variables and methods

Once inside a loop, you have the loop object avaible to use information that is specific to your current loop.

These can be called in the usual Jinja fashion, e.g.:
- {{ loop.index }},
- {% if loop.first %}...

Variable

Description

loop.index

The current iteration of the loop. (1 indexed)

loop.revindex

The number of iterations from the end of the loop (1 indexed)

loop.first

True if first iteration.

loop.last

True if last iteration.

loop.length

The number of items in the sequence.

loop.cycle

Cycle through a given list of strings or variables. E.g.: {{ loop.cycle('odd', 'even') }}

loop.previtem

The item from the previous iteration of the loop. Undefined during the first iteration.

loop.nextitem

The item from the following iteration of the loop. Undefined during the last iteration.

loop.changed(*val)

True if previously called with a different value (or not called at all).


To fix the SQL Query from the previous example, we need to use either:
- loop.first or
- loop.last

{% set values = [ 'Central','South','East','West'] %}
SELECT
{% for value in values %}
	'{{value}}' as {{ value }} 
    {% if not loop.last %},{% endif %}
{% endfor %}

Loop Examples

Looping over a Dictionary:

{% for key in dict %} 
	{{ key }}: {{ dict[key] }} 
{% endfor %}

Compare loop value to previous or next iteration:

{% for value in values %}    
	{% if loop.previtem is defined and value > loop.previtem %}
    	The value just increased! 
    {% endif %}    
    {{ value }}    
    {% if loop.nextitem is defined and loop.nextitem > value %}
    	The value will increase even more! 
    {% endif %}
{% endfor %}

Loop only if a Condition is met:

{% for value in values if values|length > 5%}
    {{ value }}    
{% endfor %}

References

- https://jinja.palletsprojects.com/en/3.1.x/templates/#for


Jump to

Start shipping smarter reports today

Sign up and start using Query.me for free.
Or schedule a demo and discuss your use case.