Presto查询引擎-运行mongoDB查询以提取JSON类型数据

Question

我正在尝试使用trino中的模式查询Mongo中的记录。

​

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

​

“个性化细节地图”是JSON格式的，它也是一个数组，它可以在它的旁边有数组。并且在“个性化细节地图”中有超过200个或更多的属性，这些属性必须被表示为列，如下所示。是否有适当的方法来提取这些字段而不重复使用json_extract_scalar(.)很多次了？

select _id as id,eventString,domaindetails,technicaldetails,processStages,personaldetailsmap,
json_extract_scalar(personaldetailsmap, '$.0.firtName.0') as firtName,
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;

Answer 1

据我所知，您希望使用Trino来平平数组字段。与每次只获取一个值不同，您可以使用` `CROSS连接UNNEST来简化这些值。下面是一个使您共享的JSON数据扁平化的示例。

select field.name,field.hidden from <table-name> CROSS JOIN UNNEST(fields) as t(field)