实时数仓 Hologres的jsonb格式，提取的数组如何转换为 int数组呢？

在实时数仓 Hologres 中，可以通过 JSONB 类型的处理函数提取出数组，并将其转换为整型数组以进行进一步的操作。以下是详细的步骤和方法：

1. 提取 JSONB 中的数组

假设 JSONB 数据存储在一个表中，字段名为 jsonb_column，其内容如下：

{
  "sku_ids": [124739738, 124739741, 124739742, 124739737, 124739740, 124739739]
}

可以使用 -> 操作符提取 sku_ids 对应的数组：

SELECT jsonb_column -> 'sku_ids' AS sku_ids_array
FROM your_table;

这将返回一个 JSONB 格式的数组。

2. 将 JSONB 数组转换为整型数组

为了对数组进行操作（如判断是否包含某个元素），需要将 JSONB 数组转换为 PostgreSQL 的原生数组类型（INT[]）。可以使用 jsonb_array_elements_text 函数结合 ARRAY_AGG 实现这一目标。

示例 SQL 如下：

SELECT ARRAY_AGG(value::INT) AS sku_ids_int_array
FROM (
    SELECT jsonb_array_elements_text(jsonb_column -> 'sku_ids') AS value
    FROM your_table
) subquery;

解释： - jsonb_array_elements_text：将 JSONB 数组展开为每一行的文本值。 - value::INT：将文本值显式转换为整型。 - ARRAY_AGG：将展开的值重新聚合为一个整型数组。

3. 判断数组是否包含指定元素

在 PostgreSQL 和 Hologres 中，可以使用 ARRAY_CONTAINS 函数或 @> 操作符来判断数组是否包含某个元素。

方法 1：使用 ARRAY_CONTAINS

SELECT ARRAY_CONTAINS(sku_ids_int_array, 124739738) AS contains_sku_id
FROM (
    SELECT ARRAY_AGG(value::INT) AS sku_ids_int_array
    FROM (
        SELECT jsonb_array_elements_text(jsonb_column -> 'sku_ids') AS value
        FROM your_table
    ) subquery
) final_query;

方法 2：使用 @> 操作符

SELECT sku_ids_int_array @> ARRAY[124739738] AS contains_sku_id
FROM (
    SELECT ARRAY_AGG(value::INT) AS sku_ids_int_array
    FROM (
        SELECT jsonb_array_elements_text(jsonb_column -> 'sku_ids') AS value
        FROM your_table
    ) subquery
) final_query;

解释： - ARRAY_CONTAINS(array, element)：检查数组是否包含指定元素，返回布尔值。 - @> 操作符：检查左侧数组是否包含右侧数组的所有元素。

4. 判断数组是否包含另一个数组中的任意元素

如果需要判断 sku_ids 是否包含另一个数组中的任意元素，可以使用 ARRAY_EXCEPT 或 UNNEST 结合 IN 子句实现。

示例：判断是否包含 [124739738, 124739741] 中的任意元素

SELECT EXISTS (
    SELECT 1
    FROM UNNEST(sku_ids_int_array) AS sku_id
    WHERE sku_id IN (124739738, 124739741)
) AS contains_any_sku_id
FROM (
    SELECT ARRAY_AGG(value::INT) AS sku_ids_int_array
    FROM (
        SELECT jsonb_array_elements_text(jsonb_column -> 'sku_ids') AS value
        FROM your_table
    ) subquery
) final_query;

解释： - UNNEST：将数组展开为多行。 - IN 子句：检查展开的值是否存在于指定的列表中。 - EXISTS：判断是否存在满足条件的行。

注意事项

1. 性能优化：如果数据量较大，建议对 JSONB 字段启用列式存储，并确保查询条件能够利用索引。
2. 稀疏数据：如果 JSONB 数据中存在大量稀疏字段，列式存储可能不会生效，需谨慎评估使用场景。
3. 版本限制：部分函数（如 TRY_CAST）仅在特定版本中支持，请确保您的 Hologres 版本符合要求。

通过上述步骤，您可以成功从 JSONB 数据中提取数组并转换为整型数组，同时实现数组的包含判断操作。