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'] %}
{% 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 %}...




The current iteration of the loop. (1 indexed)


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


True if first iteration.


True if last iteration.


The number of items in the sequence.


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


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


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


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'] %}
{% 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 %}


- 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.