在BigQuery中对比源属性与标准化属性的映射关系

需求说明 #

我们有一个大型数据集，需要从JSON中提取**源属性（source_attributes）到标准化属性（normalized）**的所有映射值，匹配规则为：当标准化值中的source_key与source_attributes的键相同时，返回对应的标准化属性名称。

输入示例 #

{
  "source_attributes": {
    "desirability": {
      "values": [
        {
          "value": "000000002"
        }
      ]
    },
    "isbn-13": {
      "values": [
        {
          "value": "9781934568392"
        }
      ]
    }
  },
  "normalized": {
    "isbn-13": [
      {
        "properties": {
          "attributeId": "85984",
          "multiselect": "N",
          "domain": "pcs",
          "display_attribute_name": "ISBN-13",
          "taxonomy_version": "urn:taxonomy:pcs2.0",
          "attributeName": "ISBN-13"
        },
        "values": [
          {
            "display_attr_name": "ISBN-13",
            "locale": "en_US",
            "value": "9781934568392",
            "isPrimary": "true",
            "source_value": "9781934568392",
            "source_key": "isbn-13"
          }
        ]
      }
    ]
  }
}

现有查询 #

目前已编写以下语句获取源属性和标准化数据，但因二者结构不同，无法完成值的匹配：

SELECT json_extract(element_json, '$.source_attributes') as source_att, json_extract(element_json, '$.normalized') as normal
FROM `data`
where json_value(element_json,'$.source')='TEST'
limit 1000;

解决方案 #

通过展开JSON嵌套结构，将源属性的键和标准化数据中的source_key进行关联，即可得到映射关系。以下是适配BigQuery的查询语句：

WITH source_attrs AS (
  -- 展开source_attributes，提取键（source_key）和对应值
  SELECT
    json_value(element_json, '$.source') AS source,
    attr_key AS source_key,
    json_value(attr_value, '$.values[0].value') AS source_value
  FROM
    `data`,
    UNNEST(json_object_keys(json_extract(element_json, '$.source_attributes'))) AS attr_key,
    UNNEST([json_extract(element_json, CONCAT('$.source_attributes.', attr_key))]) AS attr_value
  WHERE
    json_value(element_json,'$.source')='TEST'
),
normalized_attrs AS (
  -- 展开normalized结构，提取source_key和标准化属性名称
  SELECT
    json_value(element_json, '$.source') AS source,
    json_value(norm_value, '$.source_key') AS source_key,
    json_value(norm_prop, '$.display_attribute_name') AS normalized_name
  FROM
    `data`,
    -- 展开normalized的顶层键对应的数组
    UNNEST(json_object_keys(json_extract(element_json, '$.normalized'))) AS norm_key,
    UNNEST(json_query_array(element_json, CONCAT('$.normalized.', norm_key))) AS norm_item,
    -- 展开values数组
    UNNEST(json_query_array(norm_item, '$.values')) AS norm_value,
    -- 获取properties对象
    UNNEST([json_extract(norm_item, '$.properties')]) AS norm_prop
  WHERE
    json_value(element_json,'$.source')='TEST'
)
-- 关联两个CTE，匹配source_key，得到映射结果
SELECT
  sa.source_key,
  sa.source_value,
  na.normalized_name
FROM
  source_attrs sa
LEFT JOIN
  normalized_attrs na
ON
  sa.source = na.source
  AND sa.source_key = na.source_key
LIMIT 1000;

语句说明 #

• source_attrs CTE：将source_attributes的键值对展开，提取每个源属性的source_key（即原属性键）和对应的source_value。
• normalized_attrs CTE：逐层展开normalized的嵌套结构，提取每个标准化值的source_key和对应的标准化属性名称（display_attribute_name）。
• 关联查询：通过source和source_key将两个CTE关联，得到源属性到标准化属性的映射关系。

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