DBT Jinja - array contains macro

Question

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

Answer 1

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,