时间戳

首页 > 解决方案 > Concat multiple columns with filter in QueryRecord (NiFi)

apache-nifi - Concat multiple columns with filter in QueryRecord (NiFi)

问题描述

I have a JSON looks like:

[ {
  "metric_name" : "ga:bounces",
  "metric_value" : "16",
  "report_date" : "20210625",
  "utm_campaign" : "retail_group",
  "utm_source" : "(direct)",
  "utm_content" : "(not set)",
  "utm_term" : "(not set)",
  "utm_medium" : "search"
}, {
  "metric_name" : "ga:sessionDuration",
  "metric_value" : "1576.0",
  "report_date" : "20210625",
  "utm_campaign" : "rb_olv",
  "utm_source" : "google",
  "utm_content" : "(not set)",
  "utm_term" : "(not set)",
  "utm_medium" : "cpc"
}]

What I want:

[ {
  "metric_name" : "ga:bounces",
  "metric_value" : "16",
  "report_date" : "20210625",
  "utm_marks" : "utm_campaign=retail_group&utm_medium=search"
}, {
  "metric_name" : "ga:sessionDuration",
  "metric_value" : "1576.0",
  "report_date" : "20210625",
  "utm_marks" : "utm_campaign=rb_olv&utm_source=google&utm_medium=cpc"
}]

If some of utm values are inside () I should ignore it (key + value), it can be (none), (direct), (not provided), (not set)

I tried with QueryRecord:

SELECT 
metric_name,
metric_value,
'utm_campaign=' || utm_campaign || '&utm_source=' || utm_source || '&utm_medium=' || utm_medium || '&utm_term=' || utm_term || '&utm_content=' || utm_content as utm_marks
FROM FLOWFILE

But it will not truncate values like (none), (direct). How can I improve statement?

标签： apache-nifi

解决方案

QueryRecord 使用 Apache Calcite 进行 SQL 解析/评估，看看他们的 CASE 语句，你也许可以做类似的事情CASE WHEN utm_source != '(not set)' THEN '&utm_source=' || utm_source ELSE '' END

推荐阅读