如何从BigQuery的JSON类型列中提取指定字段及URI内的项目名

以下是不同主流数据库的实现代码，统一假设你存储JSON的列名为json_col，表名为your_table：

MySQL 5.7+ 版本 #

SELECT
  -- 提取resourceName，JSON_UNQUOTE用于去掉JSON_EXTRACT返回值自带的双引号
  JSON_UNQUOTE(JSON_EXTRACT(json_col, '$.overview.resourceName')) AS resourceName,
  -- 两层SUBSTRING_INDEX切割URI提取项目名
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      JSON_UNQUOTE(JSON_EXTRACT(json_col, '$.overview.resource')),
      '/projects/',
      -1
    ),
    '/',
    1
  ) AS ProjectName
FROM your_table;

-- 也可以用->>运算符简化写法，等价于JSON_UNQUOTE(JSON_EXTRACT())
SELECT
  json_col->>'$.overview.resourceName' AS resourceName,
  SUBSTRING_INDEX(SUBSTRING_INDEX(json_col->>'$.overview.resource', '/projects/', -1), '/', 1) AS ProjectName
FROM your_table;

PostgreSQL 版本 #

SELECT
  -- ->> 直接返回字符串类型的字段值
  json_col->'overview'->>'resourceName' AS resourceName,
  -- 正则匹配/projects/后的第一个路径段作为项目名，兼容性更强
  (regexp_match(json_col->'overview'->>'resource', '/projects/([^/]+)'))[1] AS ProjectName
FROM your_table;

BigQuery 版本 #

SELECT
  JSON_EXTRACT_SCALAR(json_col, '$.overview.resourceName') AS resourceName,
  REGEXP_EXTRACT(JSON_EXTRACT_SCALAR(json_col, '$.overview.resource'), r'/projects/([^/]+)') AS ProjectName
FROM your_table;

常见问题说明 #

你之前用JSON_EXTRACT没有达到预期，大概率是因为JSON_EXTRACT默认返回的是带双引号的JSON格式值，需要用对应数据库的方法转成普通字符串后再做后续处理，比如MySQL的JSON_UNQUOTE、PostgreSQL的->>运算符、BigQuery的JSON_EXTRACT_SCALAR函数都是用来解决这个问题的。

内容的提问来源于stack exchange，提问作者Andrei