下面是存储在表(table:stg,col:LN(和查询的一列中的数据,我用来访问这些列:
{"locations": [{"id": "893d7ef0", "name": "Organization", "type": "region"},
{"id": "7ad8787c", "name": "CORONA", "type": "st", "st_id": "1127"}]}
使用的查询为:
select * from(
select
replace(LN : locations.id , '"' , '')as loc_id,
replace(LN : locations.name , '"' , '') as loc_name,
replace(LN : locations.type , '"' , '') as loc_type,
replace(LN : locations.st_id , '"' , '') as loc_store_id
from db.schema.STG)
查询在列中给出 NULL,是问题所在。有什么建议吗?
你能试试这个吗?
select
replace( j.value:id , '"' , '')as loc_id,
replace( j.value:name , '"' , '') as loc_name,
replace( j.value:type , '"' , '') as loc_type,
replace( j.value:st_id , '"' , '') as loc_store_id
from STG, table(flatten ( LN, path => 'locations' )) j;