How to add column name in single string?-CodePudding

I have a code where I'm flattening the jsonb columns so I have written a code something like this

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

So when you compile the dbt then its giving me a structure like this

select
_airbyte_data,

_airbyte_data->>simid as simid,

_airbyte_data->>lease_season_None as lease_season_None,

So I'm getting this error because the column name is not in single string. How do I deal with this?

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

the airbyte data looks like this

{"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"}

CodePudding user response：

JSON field names need to be wrapped in single quotes to be selected in postgresql so you can change your Jinja to have single quotes around the column name.

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

Or update your query to include the single quotes in the initial SELECT

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