dbt中用SQL实现列值拆分为行及获取位置的报错求助

先搞清楚你用的是哪种数据仓库——不同仓库的数组拆分语法差得远，这是你报错的核心原因。以下是主流仓库的正确dbt模型写法，同时解决你提到的两个报错问题：

主流数据仓库的正确写法 #

1. Snowflake #

Snowflake支持UNNEST，直接用就能拆分还能拿位置：

WITH split_data AS (
    SELECT
        id,
        value AS split_value,
        INDEX AS position
    FROM sample_data,
         UNNEST(content_toSplit) WITH INDEX AS value
)
SELECT * FROM split_data;

嫌上面的写法乱？用LATERAL JOIN更清晰：

WITH split_data AS (
    SELECT
        sd.id,
        u.value AS split_value,
        u.index AS position
    FROM sample_data sd
    LATERAL JOIN UNNEST(sd.content_toSplit) WITH INDEX u
)
SELECT * FROM split_data;

2. BigQuery #

BigQuery用UNNEST搭配OFFSET()拿位置：

WITH split_data AS (
    SELECT
        id,
        split_value,
        OFFSET(split_value) AS position
    FROM sample_data,
         UNNEST(content_toSplit) AS split_value
)
SELECT * FROM split_data;

3. PostgreSQL #

PostgreSQL用WITH ORDINALITY直接带位置，或者用下标生成函数：

-- 方法1：最直观的写法
WITH split_data AS (
    SELECT
        sd.id,
        u.split_value,
        u.position
    FROM sample_data sd
    LEFT JOIN LATERAL unnest(sd.content_toSplit) WITH ORDINALITY u(split_value, position) ON true
)
SELECT * FROM split_data;

-- 方法2：用下标生成
WITH split_data AS (
    SELECT
        id,
        content_toSplit[i] AS split_value,
        i AS position
    FROM sample_data,
         generate_subscripts(content_toSplit, 1) AS i
)
SELECT * FROM split_data;

4. Redshift #

Redshift不支持UNNEST，得用递归CTE拆：

WITH RECURSIVE split_data AS (
    SELECT
        id,
        content_toSplit AS remaining_array,
        1 AS position,
        JSON_EXTRACT_ARRAY_ELEMENT_TEXT(content_toSplit, 0) AS split_value
    FROM sample_data
    WHERE JSON_ARRAY_LENGTH(content_toSplit) > 0
    UNION ALL
    SELECT
        id,
        JSON_REMOVE_ELEMENT(remaining_array, 0) AS remaining_array,
        position + 1 AS position,
        JSON_EXTRACT_ARRAY_ELEMENT_TEXT(remaining_array, 0) AS split_value
    FROM split_data
    WHERE JSON_ARRAY_LENGTH(remaining_array) > 1
)
SELECT id, split_value, position
FROM split_data
ORDER BY id, position;

你的报错原因及解决 #

1. "SQL compilation error: Object 'UNNEST' does not exist or not authorized"
   • 要么是你用的仓库不支持UNNEST（比如Redshift），要么是语法没写对（比如没加LATERAL JOIN），再或者是你没权限访问相关对象。
2. "unexpected '('. syntax error line"
   • 纯纯是UNNEST的写法错了，比如你在CTE里直接写UNNEST(content_toSplit)但没和原表关联，或者括号位置写错了，按上面对应仓库的写法改就行。

额外技巧：用dbt-utils宏适配多仓库 #

如果需要跨仓库兼容，装个dbt-utils包，用它自带的unnest宏，自动适配不同仓库：

-- 先跑dbt deps安装依赖
WITH split_data AS (
    SELECT
        id,
        value AS split_value,
        position
    FROM sample_data,
         {{ dbt_utils.unnest('content_toSplit', with_offset=True) }}
)
SELECT * FROM split_data;

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