CODEWITHSUNDEEP
jinja2dbt
DataMan
DataMan

Reputation: 3455

How to remove brackets from Jinja list variable (DBT)

I have a list variable created like this: {% set options = ["a", "b", "c"] %}

I want to use it in a SQL CTE like this:

df as (
 select *
 from my_table
 pivot (sum(value) for option in ({{options}}))
)

When the SQL is compiled by DBT, the result is:

df as (
 select *
 from my_table
 pivot (sum(value) for option in (["a", "b", "c"]))
)

And that won't work because of the brackets. So how can I use the list variable without including the brackets?

Upvotes: 3

Views: 2347

Answers (3)

DataMan
DataMan

Reputation: 3455

As a workaround I ended up changing my Jinja variable to a tuple like this:

{% set options = ("a", "b", "c") %}

I will select one of the other answers as the best answer.

Upvotes: 1

tconbeer
tconbeer

Reputation: 5805

@larsks is right that you want to create a string from your list, not change the way the list is displayed.

The easiest way to do this is to use the join filter in jinja:

{% set options = ["a", "b", "c"] %}
{{ options | join(", ")}}
-- compiles to:
a, b, c

That works fine for numbers, but to get string literals into your sql query, you'll need to quote the values in your list. I prefer to do this by adding nested quotes in the list itself:

{% set options = ["'a'", "'b'", "'c'"] %}
{{ options | join(", ")}}
-- compiles to:
'a', 'b', 'c'

But you can also put the extra quotes inside the argument to join, and concatenate an extra quote to the beginning and end of your string:

{% set options = ["a", "b", "c"] %}
{{ "'" ~ options | join("', '") ~ "'"}}
-- compiles to:
'a', 'b', 'c'

Or you can wrap you jinja expression in a single quote to achieve the same thing, but I think this is hard to read::

{% set options = ["a", "b", "c"] %}
'{{ options | join("', '") }}'
-- compiles to:
'a', 'b', 'c'

Upvotes: 3

larsks
larsks

Reputation: 311750

You're asking for the string representation of a list variable; that's always going to include the brackets. If you want something different you need to take a more active role in formatting the data.

The general recommendation for this sort of thing is to format the string in your code before rendering the template. E.g:

import jinja2


options = ["a", "b", "c"]

template = jinja2.Template('''
df as (
 select *
 from my_table
 pivot (sum(value) for option in ({{options}}))
)
''')

print(template.render(options=', '.join(f'"{x}"' for x in options)))

If you can't do that, here's on option:

import jinja2


template = jinja2.Template('''
{%- set options = ["a", "b", "c"] -%}
{%- set comma = joiner(", ") -%}
df as (
 select *
 from my_table
 pivot (sum(value) for option in ({% for option in options %}{{ comma() }}"{{ option }}"{% endfor %})
)
''')

print(template.render())

Which outputs:

df as (
 select *
 from my_table
 pivot (sum(value) for option in ("a", "b", "c")
)

Upvotes: 1

Related Questions