如何解决jsonb列展平后字段自动转为文本类型的问题？

问题描述 #

我编写了一个dbt宏用于展平JSONB列，宏接收model_name（数据源模型）和json_column（存储JSON数据的列）两个参数。目前展平后所有字段均为text类型，推测是使用->>操作符（会将所有值转换为字符串）导致的。我需要让展平后的字段类型与原JSON中的数据类型保持一致，字符串保留text类型，数字、布尔值等也维持原生类型。

当前使用的宏代码如下：

{% macro flatten_json(model_name, json_column) %}  
  
{% set survey_methods_query %}

SELECT DISTINCT(jsonb_object_keys({{json_column}})) as column_name
from {{model_name}}
{% 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_ab_id,
_airbyte_data,
{% for column_name in results_list %}
_airbyte_data ->>'{{ column_name }}' as {{ column_name }}{% if not loop.last %},{% endif %}
{% endfor %}
from {{model_name}}
{% endmacro %}

解决方案 #

要保留JSON原生数据类型，需替换->>操作符，改用->返回JSONB类型，再通过jsonb_typeof判断字段类型并做对应转换。修改后的宏会自动检测每个JSON字段的类型，转换为PostgreSQL对应的原生类型：

{% macro flatten_json(model_name, json_column) %}  
{% set survey_methods_query %}
SELECT DISTINCT jsonb_object_keys({{ json_column }}) as column_name
FROM {{ model_name }}
{% endset %}

{% set results = run_query(survey_methods_query) %}

{% if execute %}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}

SELECT
    _airbyte_ab_id,
    _airbyte_data,
    {% for column_name in results_list %}
    CASE jsonb_typeof({{ json_column }} -> '{{ column_name }}')
        WHEN 'string' THEN {{ json_column }} ->>'{{ column_name }}'::text
        WHEN 'number' THEN ({{ json_column }} ->>'{{ column_name }}')::numeric
        WHEN 'boolean' THEN ({{ json_column }} ->>'{{ column_name }}')::boolean
        WHEN 'null' THEN NULL
        -- 若需处理数组、嵌套对象等复杂类型，可在此添加分支逻辑
        ELSE {{ json_column }} ->>'{{ column_name }}'::text
    END AS {{ column_name }}{% if not loop.last %},{% endif %}
    {% endfor %}
FROM {{ model_name }}
{% endmacro %}

关键修改点 #

• 使用jsonb_typeof检测每个JSON字段的类型
• 根据类型做针对性转换：字符串保留text，数字转numeric，布尔值转boolean，null值直接返回NULL
• 针对数组、嵌套对象等复杂类型，可根据业务需求扩展CASE分支（比如保留为jsonb类型或进一步拆解）

补充说明 #

修改后的宏会自动适配原JSON中的数据类型，避免统一为text带来的后续类型转换问题。如果需要处理日期这类特殊字符串类型，可在WHEN 'string'分支中添加格式判断，将符合日期格式的字符串转换为date/timestamp类型。

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