如何在单个字符串中添加列名？

Question

我有一个代码，在这里我正在扁平jsonb列，所以我编写了一个类似这样的代码

{% 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')}}

所以当编译dbt时，它给了我这样的结构

select
_airbyte_data,

_airbyte_data->>simid as simid,

_airbyte_data->>lease_season_None as lease_season_None,

所以我得到了这个错误，因为列名不是在单个字符串中。我该怎么处理呢？

 column "simid" does not exist
08:29:11    LINE 23: _airbyte_data->>simid as simid,

空字节数据如下所示

{"KEY": "59dcc7la-4222-46ba-83a5-f59a5f78d656", "shg": "0", "didi": "G (non-official)", "loan": "0", "simid": "89918560200035541720", "aadhar": "1", "caseid": "", "endtime": "Mar 30, 2022 11:12:30 AM", "form_id": "baseline_ig ", "hh_size": "5", "savings": "0", "bank_acc": "0"}

Answer 1

JSON字段名需要包装在单引号中，以便在postgresql中选择，这样您就可以将Jinja更改为在列名周围有单引号。

_airbyte_data->>'{{ column_name }}' as {{ column_name }}

或更新查询以将单引号包含在初始选择中。

SELECT concat('''', DISTINCT(jsonb_object_keys(_airbyte_data)), '''') as column_name