regex Snowflake仅在JSON数据字段中获取必填字段(如果可用

tcbh2hod 于 2023-06-07 发布在其他

关注(0)|答案(1)|浏览(100)

我有一个 snowflake 表如下
emp_data：
| id| emp_name|社会场|
| - -----|- -----|- -----|
| 1|湿婆|[{“social_media”：{“facebook”：“user_name”，“twitter”：“twitteR_name”}}}，{“custinfo”：{“customer_id”：“999888777”，“status”：“active”}}}，{“custinfo”：{“customer_id”：“999888777”，“chat”：“how are you”}}}，{“custinfo”：{“customer_id”：“999888777”，“chat”：“I am Good”}}}]|
| 2|基兰|[{“社交媒体”：{“facebook”：“user_name”，“twitter”：“twitteR_name”}}，{“custinfo”：{“custinfo”：{“customer_id”：“111222333”，“status”：“active”}}}|
| 3|莫汉|[{“social_media”：{“facebook”：“user_name”，“twitter”：“twitteR_name”}}，{“social_media”：{“facebook”：“user_name”，“twitter”：“twitteR_name”}}]|
| 4|哈里||
我试图从eixsting social_field json数据中获取customer_id，如下所示
| id| emp_name|社会场|客户ID|
| - -----|- -----|- -----|- -----|
| 1|湿婆|[{“social_media”：{“facebook”：“user_name”，“twitter”：“twitteR_name”}}}，{“custinfo”：{“customer_id”：“999888777”，“status”：“active”}}}，{“custinfo”：{“customer_id”：“999888777”，“chat”：“how are you”}}}，{“custinfo”：{“customer_id”：“999888777”，“chat”：“I am Good”}}}]| 999888777|
| 2|基兰|[{“社交媒体”：{“facebook”：“user_name”，“twitter”：“twitteR_name”}}，{“custinfo”：{“custinfo”：{“customer_id”：“111222333”，“status”：“active”}}}| 111222333|
| 3|莫汉|[{“social_media”：{“facebook”：“user_name”，“twitter”：“twitteR_name”}}，{“social_media”：{“facebook”：“user_name”，“twitter”：“twitteR_name”}}]| 0|
| 4|哈里||0|
我正在尝试使用REGEXP_SUBSTR进行以下查询，但在customer_id字段中得到null

select *,REGEXP_SUBSTR(social_field, '("customer_id":"([^"]+)")', 1, 1, 'e', 2)  as cust_id
from emp_data;

和/或

SELECT
  id,
  emp_name,
  social_field,
  COALESCE(JSON_VALUE(social_field, '$[1].custinfo.custinfo.customer_id'), '0') AS customer_id
FROM
  your_table

调整REGEXP_SUBSTR以获得所需值的任何帮助。

regex

来源：https://stackoverflow.com/questions/76364604/snowflake-take-only-required-field-in-json-data-field-if-it-is-available

1条答案

按热度按时间

z0qdvdin1#

这可以使用flatten来完成，因为每个emp_name有多个customer_id，所以我们可以使用group by和max()只提取一个：

select e.*, coalesce(s.customer_id, 0) as customer_id
from emp_data e
left join (
  select t.id, max(a.value:custinfo:custinfo:customer_id::varchar) as customer_id
  from emp_data t
  , lateral flatten(input => parse_json(t.social_field)) a
  group by t.id
) as s on s.id = e.id;

赞(0）回复(0）举报 2023-06-07

我来回答

regex Snowflake仅在JSON数据字段中获取必填字段(如果可用

1条答案

相关问题

热门标签

最新问答