雪花将属性值旋转到对象数组中的列 [英] snowflake pivot attribute values into columns in array of objects

问题描述

编辑：我给出了错误的示例数据。已更新一些详细信息，并为经过清理的实际数据换出了虚拟数据。

源系统：Freshdesk通过Stitch

表结构：

create or replace TABLE TICKETS (
    CC_EMAILS VARIANT,
    COMPANY VARIANT,
    COMPANY_ID NUMBER(38,0),
    CREATED_AT TIMESTAMP_TZ(9),
    CUSTOM_FIELDS VARIANT,
    DUE_BY TIMESTAMP_TZ(9),
    FR_DUE_BY TIMESTAMP_TZ(9),
    FR_ESCALATED BOOLEAN,
    FWD_EMAILS VARIANT,
    ID NUMBER(38,0) NOT NULL,
    IS_ESCALATED BOOLEAN,
    PRIORITY FLOAT,
    REPLY_CC_EMAILS VARIANT,
    REQUESTER VARIANT,
    REQUESTER_ID NUMBER(38,0),
    RESPONDER_ID NUMBER(38,0),
    SOURCE FLOAT,
    SPAM BOOLEAN,
    STATS VARIANT,
    STATUS FLOAT,
    SUBJECT VARCHAR(16777216),
    TAGS VARIANT,
    TICKET_CC_EMAILS VARIANT,
    TYPE VARCHAR(16777216),
    UPDATED_AT TIMESTAMP_TZ(9),
    _SDC_BATCHED_AT TIMESTAMP_TZ(9),
    _SDC_EXTRACTED_AT TIMESTAMP_TZ(9),
    _SDC_RECEIVED_AT TIMESTAMP_TZ(9),
    _SDC_SEQUENCE NUMBER(38,0),
    _SDC_TABLE_VERSION NUMBER(38,0),
    EMAIL_CONFIG_ID NUMBER(38,0),
    TO_EMAILS VARIANT,
    PRODUCT_ID NUMBER(38,0),
    GROUP_ID NUMBER(38,0),
    ASSOCIATION_TYPE NUMBER(38,0),
    ASSOCIATED_TICKETS_COUNT NUMBER(38,0),
    DELETED BOOLEAN,
    primary key (ID)
);

请注意变量字段"CUSTOM_FIELS"。它在API和雪花之间经历了不幸的转变。结果字段包含一个由3个或更多对象组成的数组，每个对象都有一个自定义字段。我没有能力更改数据格式。示例：

# values could be null
[
  {
    "name": "cf_request",
    "value": "none"
  },
  {
    "name": "cf_related_with",
    "value": "none"
  },
  {
    "name": "cf_question",
    "value": "none"
  }
]

# or values could have a combination of null and non-null values
[
  {
    "name": "cf_request",
    "value": "none"
  },
  {
    "name": "cf_related_with",
    "value": "none"
  },
  {
    "name": "cf_question",
    "value": "concern"
  }
]

# or they could all have non-null values
[
  {
    "name": "cf_request",
    "value": "issue with timer"
  },
  {
    "name": "cf_related_with",
    "value": "timer stopped"
  },
  {
    "name": "cf_question",
    "value": "technical problem"
  }
]

实际上，我希望将这些转换为SELECT查询中的字段，在该查询中，name属性值将成为列标题。使输出类似于以下内容：

+----+------------------+-----------------+-------------------+-----------------------------+
| id |    cf_request    | cf_related_with |    cf_question    |      all_other_fields       |
+----+------------------+-----------------+-------------------+-----------------------------+
|  5 | issue with timer | timer stopped   | technical problem | more data about this ticket |
|  6 | hq               | laptop issues   | some value        | more data                   |
|  7 | a thing          | about a thing   | about something   | more data                   |
+----+------------------+-----------------+-------------------+-----------------------------+

是否有一个函数可以搜索数组对象的值并返回具有限定值的对象？类似于：

select
id,
get_object_where(name = 'category', value) as category,
get_object_where(name = 'subcategory', value) as category,
get_object_where(name = 'subsubcategory', value) as category
from my_data_table

不幸的是，Pivot需要聚合函数，我尝试使用min和max，但只返回空值。如果有另一种不需要聚合的语法来执行此操作，则与此方法类似的方法将是非常好的。

with arr as (
  select
  id, 
  cs.value:name col_name, 
  cs.value:value col_value
  from my_data_table,
    lateral flatten(input => custom_fields) cs
)
select 
*
from arr 
pivot(col_name for col_value in ('category', 'subcategory', 'subsubcategory')
as p (id, category, subcategory, subsubcategory);

可以使用以下方法，但它存在缺陷，因为每当添加新的自定义字段时，我都必须添加大小写来说明数组中的新位置。

select
    id, 
    case 
      when custom_fields[0]:name = 'cf_request' then custom_fields[0]:value
      when custom_fields[1]:name = 'cf_request' then custom_fields[1]:value
      when custom_fields[2]:name = 'cf_request' then custom_fields[2]:value
      when custom_fields[2]:name = 'cf_request' then custom_fields[3]:value
      else null
    end cf_request,
    case 
      when custom_fields[0]:name = 'cf_related_with' then custom_fields[0]:value
      when custom_fields[1]:name = 'cf_related_with' then custom_fields[1]:value
      when custom_fields[2]:name = 'cf_related_with' then custom_fields[2]:value
      when custom_fields[2]:name = 'cf_related_with' then custom_fields[3]:value
      else null
    end cf_related_with,
    case 
      when custom_fields[0]:name = 'cf_question' then custom_fields[0]:value
      when custom_fields[1]:name = 'cf_question' then custom_fields[1]:value
      when custom_fields[2]:name = 'cf_question' then custom_fields[2]:value
      when custom_fields[2]:name = 'cf_question' then custom_fields[3]:value
      else null
    end cf_question,
    created_at
from my_db.my_schema.tickets;

推荐答案

我认为您几乎成功了。您只需在colname周围添加一个max()或min()即可。正如您所说的，它需要一个聚合函数，这里可以使用max()或min()之类的函数，因为它是在您拥有的名称/值对上聚合的。例如，如果您有2个子类别值，它将选择最小/最大值。从您的示例来看，这似乎不是问题，所以它总是会选择您想要的值。我能够使用以下查询复制您的方案：

WITH x AS (
    SELECT parse_json('[{"name": "category","value": "Bikes"},{"name": "subcategory","value": "Mountain Bikes"},{"name": "subsubcategory","value": "hardtail bikes"}]')::VARIANT as field_var
    ),
arr as (
  select
  seq,
  cs.value:name::varchar col_name, 
  cs.value:value::varchar col_value
  from x,
    lateral flatten(input => x.field_var) cs
)
select 
*
from arr
pivot(max(col_value) for col_name in ('category','subcategory','subsubcategory')) as p (seq, category, subcategory, subsubcategory);

这篇关于雪花将属性值旋转到对象数组中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！