CODEWITHSUNDEEP
postgresqljinja2dbtwarehouse
shoop
shoop

Reputation: 456

DBT Jinja - array contains macro

Is there a jinja equivalent to postgres's json_array_elements(field). I'm trying to move as much aggregation into the models as possible to avoid excessive joins or subqueries in my adhoc BI queries

trying to fit this in a case statement like so

 CASE
        WHEN {{'foo' in json_array_elements(flags)}} THEN 1
        ELSE 0 
 END AS is_foo

flags is a json field containing an array

flags
['foo', 'bar']

been trying different things like fromjson() and if/else blocks without luck

Upvotes: 0

Views: 1237

Answers (1)

shoop
shoop

Reputation: 456

I was attempting to use postgres functions in the staging models.I was able to use native functions in the /models/marts but not /models/staging. no Jinja required

 case when 'foo' in (select * from json_array_elements_text(flags)) then 1
        else 0 
        end as is_foo,

Upvotes: 1

Related Questions