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

Question

我正试图在Mongo中查询一条记录，该记录在trino中具有以下模式。

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

"个性化细节地图"；是JSON格式的，它也是一个数组，可以在它的旁边有数组；个性化细节地图"；必须将其表示为如下查询中所示的列。有没有合适的方法可以在不重复多次使用json_extract_scaler(..(的情况下提取这些字段？

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 JOIN UNNEST来压平这些值，而不是一次获取一个值。下面是一个将您共享的JSON数据扁平化的示例。

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