如何在dbt中为JSONB字段提取的列名添加单引号？

你当前用dbt展平JSONB类型的_airbyte_data列时，生成的SQL里->>操作符后的JSON键名没有被单引号包裹，PostgreSQL会把这些键名识别为数据库列名，因此抛出column "simid" does not exist的错误。

修复方案 #

只需要在循环生成字段引用的代码中，给动态获取的键名加上单引号即可。修改后的核心循环代码如下：

{% for column_name in results_list %}
_airbyte_data->>'{{ column_name }}' as {{ column_name }}{% if not loop.last %},{% endif %}
{% endfor %}

修改后的完整dbt代码 #

{% set survey_methods_query %}

SELECT DISTINCT(jsonb_object_keys(_airbyte_data)) as column_name 
from {{source('survey-cto', 'raw_surveycto')}}
{% endset %}

{% set results = run_query(survey_methods_query) %}

{% if execute %}
{# Return the first column #}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}

select
_airbyte_data,
{% for column_name in results_list %}
_airbyte_data->>'{{ column_name }}' as {{ column_name }}{% if not loop.last %},{% endif %}
{% endfor %}
from {{source('survey-cto', 'raw_surveycto')}}

编译后生成的正确SQL #

修改后编译出的SQL会自动给JSON键名加上单引号，格式示例如下：

select
_airbyte_data,

_airbyte_data->>'simid' as simid,

_airbyte_data->>'lease_season_None' as lease_season_None,
...

此时PostgreSQL能正确识别这些是JSONB字段的键名，而非数据库列名，报错问题即可解决。

内容的提问来源于stack exchange，提问作者Siddhant Singh