Skip to content

How do I filter a list through a lookup dictionary in jinja2

How can I filter a list in jinja2 to generate a list of values from a lookup table?

I have a situation where I am using Jinja2 to generate SQL. I have a list of values and I want to replace them with a list of transformed values based on a lookup:

start_values = ['a', 'c']
lookup_table = {
    'a': {
        'table_x': '11111',
        'table_y': '22222'
    },
    'b': {
        'table_x': '33333',
        'table_y': '44444'
    },
    'c': {
        'table_x': '55555',
        'table_y': '66666'
    }
}

In my template, I want to be able to use do something like this using filters instead of a loop:

GROUP BY
{% for col in start_values %}
  {{ lookup_table[col]['table_x'] }},
{% endif %}

The trailing comma will lead to SQL that is not well formed.

I want to avoid creating a customer filter, but I haven’t come across a filter that can find a dictionary reference the way I need:

{{ start_values|some_lookup_method(lookup_table)|map(attribute='table_x')|join(', ') }}

I can’t use map(attribute=*) for some_lookup_method because I don’t know how to access the key that comes from start_values. Most examples online are for Ansible and use a flattened structure, like the one below. I do not want that structure so please no answers for that format.

# Answers for this format are already plentiful on the Internet
lookup_table = [
    {
        'name': 'a',
        'table_x': '11111',
        'table_y': '22222'
    },
    # ...
]

Answer

There is, sadly, no such a filter in native Jinja to filter a dictionary or to make a list out of a dictionary, like the answers you found for Ansible, that are using dict2items.

This said, using loop.last, you could conditionally add the comma or not:

GROUP BY
{%- for col in start_values %}
  {{ lookup_table[col].table_x }}{% if not loop.last %},{% endif %}
{%- endfor %}

Would give you

GROUP BY
  11111,
  55555