时间戳

首页 > 解决方案 > 从单元格中的 JSON 中提取最后一项

google-bigquery - 从单元格中的 JSON 中提取最后一项

问题描述

我有一个名为submission_datejson 单元格的列，如下所示：

{"submitted":["January 24, 2019","January 25, 2019","January 30,
2019","February 27, 2019"],"submission_canceled":["January 24, 
2019","January 25, 2019"],"returned":"February 19, 2019"}

或像这样：

{"submitted":["February 27, 2019","March 5, 2019"],"submission_canceled":"March 5, 2019"}

通过执行以下操作，我可以轻松地从“submission_canceled”字段中获得第一个结果：

json_extract(submission_date, "$.submission_canceled[0]")

我想如果我想保持最后的价值，我会这样做：

json_extract(submission_date, "$.submission_canceled[-1]")

但这只是给我一个空值。如您所见，有时该submission_canceled字段在列表中会有多个日期，而有时它只有一个日期，而不是在列表中。我想从该submission_canceled部分获取列表中的单个项目或最后一个项目。

标签： google-bigquery

解决方案

以下示例适用于 BigQuery 标准 SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '{"submitted":["January 24, 2019","January 25, 2019","January 30, 2019","February 27, 2019"],"submission_canceled":["January 24,  2019","January 25, 2019"],"returned":"February 19, 2019"}' submission_date UNION ALL
  SELECT 2, '{"submitted":["February 27, 2019","March 5, 2019"],"submission_canceled":"March 5, 2019"}'
)
SELECT id, REGEXP_REPLACE(ARRAY_REVERSE(SPLIT(JSON_EXTRACT(submission_date, '$.submission_canceled'), '","'))[OFFSET(0)], r'"|\[|\]', '') last_submission_canceled
FROM `project.dataset.table`

结果 

Row id  last_submission_canceled     
1   1   January 25, 2019     
2   2   March 5, 2019

更新 - 下面是“更轻”的版本 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '{"submitted":["January 24, 2019","January 25, 2019","January 30, 2019","February 27, 2019"],"submission_canceled":["January 24,  2019","January 25, 2019"],"returned":"February 19, 2019"}' submission_date UNION ALL
  SELECT 2, '{"submitted":["February 27, 2019","March 5, 2019"],"submission_canceled":"March 5, 2019"}'
)
SELECT id, REGEXP_EXTRACT(JSON_EXTRACT(submission_date, '$.submission_canceled'), r'"([^"]*)"\]?$') last_submission_canceled
FROM `project.dataset.table`

结果明显相同 

Row id  last_submission_canceled     
1   1   January 25, 2019     
2   2   March 5, 2019

推荐阅读