Presto查询MongoDB：高效提取JSON数组字段的批量方法

我正在使用Presto查询MongoDB中的数据，目标集合的Schema如下：

{
    "_id": {
        "$oid": "123456789010111213"
    },
    "table": "personaldatacollection",
    "fields": [
        {
            "name": "eventString",
            "type": "row(..)",
            "hidden": false
        },           
        {
            "name": "personaldetailsmap",
            "type": "JSON",
            "hidden": false
        }
    ]
}

其中personaldetailsmap是JSON格式的数组，内部包含嵌套数组，且有200余个属性，需要将这些属性转换为列。目前我通过重复调用json_extract_scalar函数实现，但想找到更合适的方法避免大量重复代码。当前使用的查询语句如下：

select _id as id,eventString,domaindetails,technicaldetails,processStages,personaldetailsmap,
json_extract_scalar(personaldetailsmap, '$.0.firstName.0') as firstName,
json_extract_scalar(personaldetailsmap, '$.0.middleName.0') as middleName,
json_extract_scalar(personaldetailsmap, '$.0.lastName.0') as lastName,
json_extract_scalar(personaldetailsmap, '$.0.initials.0') as initials,
json_extract_scalar(personaldetailsmap, '$.0.age.0') as age,
json_extract_scalar(personaldetailsmap, '$.0.birthMonth.0') as birthMonth,
json_extract_scalar(personaldetailsmap, '$.0.birthDate.0') as birthDate,
json_extract_scalar(personaldetailsmap, '$.0.birthYear.0') as birthYear,
...
from "test".db01.personaldatacollection;

请问是否存在无需重复调用json_extract_scalar的高效提取方法？

针对Presto中提取JSON数组内属性转列的需求，有以下几种高效方法避免重复代码：

方法1：JSON解析+行类型转换 #

先将personaldetailsmap解析为JSON数组，提取首个元素后转换为预定义的行类型，后续直接从行对象中提取属性：

WITH parsed_data AS (
    SELECT 
        _id as id,
        eventString,
        domaindetails,
        technicaldetails,
        processStages,
        personaldetailsmap,
        -- 解析JSON数组并提取第一个元素，转换为包含所有属性的行类型
        cast(json_parse(personaldetailsmap) AS array(row(
            firstName array(varchar),
            middleName array(varchar),
            lastName array(varchar),
            initials array(varchar),
            age array(varchar),
            birthMonth array(varchar),
            birthDate array(varchar),
            birthYear array(varchar),
            -- 依次添加剩余200+属性的定义，格式为`属性名 array(数据类型)`
        )))[1] AS personal_details
    FROM "test".db01.personaldatacollection
)
SELECT 
    id,
    eventString,
    domaindetails,
    technicaldetails,
    processStages,
    personaldetailsmap,
    -- 直接从行对象中取属性，再取数组第一个值
    personal_details.firstName[1] AS firstName,
    personal_details.middleName[1] AS middleName,
    personal_details.lastName[1] AS lastName,
    personal_details.initials[1] AS initials,
    personal_details.age[1] AS age,
    personal_details.birthMonth[1] AS birthMonth,
    personal_details.birthDate[1] AS birthDate,
    personal_details.birthYear[1] AS birthYear,
    -- 其他属性按同样方式提取
FROM parsed_data;

优势：仅需一次JSON解析操作，代码结构更整洁；通过行类型定义实现类型校验，能提前发现属性类型不匹配问题。

方法2：键值对展开+条件聚合（适合属性动态场景） #

如果personaldetailsmap的属性不固定，或者不想预定义行类型，可以将JSON对象拆解为键值对，再通过条件聚合转成列：

WITH key_value_data AS (
    SELECT 
        _id as id,
        eventString,
        domaindetails,
        technicaldetails,
        processStages,
        personaldetailsmap,
        entry.key AS prop_name,
        -- 取出属性对应数组的第一个值
        json_array_get(entry.value, 0) AS prop_value
    FROM "test".db01.personaldatacollection,
         -- 解析JSON数组首个元素为map，再拆解为键值对
         UNNEST(map_entries(cast(json_parse(personaldetailsmap)[1] AS map(varchar, json)))) AS t(entry)
)
SELECT 
    id,
    eventString,
    domaindetails,
    technicaldetails,
    processStages,
    personaldetailsmap,
    MAX(CASE WHEN prop_name = 'firstName' THEN prop_value END) AS firstName,
    MAX(CASE WHEN prop_name = 'middleName' THEN prop_value END) AS middleName,
    MAX(CASE WHEN prop_name = 'lastName' THEN prop_value END) AS lastName,
    MAX(CASE WHEN prop_name = 'initials' THEN prop_value END) AS initials,
    MAX(CASE WHEN prop_name = 'age' THEN prop_value END) AS age,
    -- 其他属性添加对应的CASE条件即可
FROM key_value_data
GROUP BY id, eventString, domaindetails, technicaldetails, processStages, personaldetailsmap;

优势：无需提前定义所有属性，适配属性动态变化的场景；仅需维护CASE条件部分，比重复调用json_extract_scalar更简洁。

方法3：使用json_path_query简化路径表达式 #

如果所有属性的JSON路径都是$.0.xxx.0格式，可以用json_path_query替代json_extract_scalar，语法更简洁：

SELECT 
    _id as id,
    eventString,
    domaindetails,
    technicaldetails,
    processStages,
    personaldetailsmap,
    json_path_query(personaldetailsmap, '$.0.firstName[0]') AS firstName,
    json_path_query(personaldetailsmap, '$.0.middleName[0]') AS middleName,
    json_path_query(personaldetailsmap, '$.0.lastName[0]') AS lastName,
    -- 其他属性按同样格式添加
FROM "test".db01.personaldatacollection;

优势：路径表达式可读性更强，比json_extract_scalar的语法更直观，减少重复代码的冗余感。

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