select
GET_JSON_OBJECT(name_tmp,'$.val') as val
,GET_JSON_OBJECT(name_tmp,'$.area') as area
,GET_JSON_OBJECT(name_tmp,'$.setVal') as setVal
,GET_JSON_OBJECT(name_tmp,'$.isExceed') as isExceed
from (
-- 删除前后的中括号并修改分隔符
select
regexp_replace(regexp_replace(regexp_replace(json_str,'^\\[',''),'\\]$',''),'},\\{','}|{') as json_str1
from (
select '[{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":7,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":7,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":1,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":7,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":6,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":5,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""},{"val":6,"area":"测区1","setVal":"","isExceed":false,"isQualified":true,"x":"","y":""}]' as json_str
lateral view explode(split(json_str1,'\\|')) b AS name_tmp
+------------+------------+------------+------------+
| val | area | setval | isexceed |
+------------+------------+------------+------------+
| 5 | 测区1 | | false |
| 7 | 测区1 | | false |
| 7 | 测区1 | | false |
| 1 | 测区1 | | false |
| 5 | 测区1 | | false |
| 7 | 测区1 | | false |
| 5 | 测区1 | | false |
| 6 | 测区1 | | false |
| 5 | 测区1 | | false |
| 5 | 测区1 | | false |
| 5 | 测区1 | | false |
| 6 | 测区1 | | false |
+------------+------------+------------+------------+
,area
,concat_ws(
'
,
'
,collect_list(val))
as
val_list
,
avg
(
case
when
rn1
>
3
and
rn2
>
3
then
val
end
)
as
avg_val
,
max
(fck)
as
fck
,
max
(avg_val)
as
avg_val
,
max
(carbon)
as
carbon
,
max
(is_new)
as
is_new
from
(
select
,area
,setVal
,isExceed
,avg_val
,carbon
,
case
when
nvl(fck,
''
)
=
''
then
0
else
1
end
as
is_new
--
0为旧,1为新
,row_number()
over
(partition
by
id,area
order
by
val
asc
)
as
rn1
,row_number()
over
(partition
by
id,area
order
by
val
desc
)
as
rn2
from
(
select
,GET_JSON_OBJECT(name_tmp,
'
$.val
'
)
as
val
,GET_JSON_OBJECT(name_tmp,
'
$.area
'
)
as
area
,GET_JSON_OBJECT(name_tmp,
'
$.setVal
'
)
as
setVal
,GET_JSON_OBJECT(name_tmp,
'
$.isExceed
'
)
as
isExceed
,GET_JSON_OBJECT(name_tmp,
'
$.fck
'
)
as
fck
--
强度值
,GET_JSON_OBJECT(name_tmp,
'
$.avg
'
)
as
avg_val
--
平均值
,GET_JSON_OBJECT(name_tmp,
'
$.carbon
'
)
as
carbon
--
碳化深度
from
(
--
删除前后的中括号并修改分隔符
select
,regexp_replace(regexp_replace(regexp_replace(json_str,
'
^\\[
'
,
''
),
'
\\]$
'
,
''
),
'
},\\{
'
,
'
}|{
'
)
as
json_str1
from
(
select
id,json_str
from
json_demo_02
lateral
view
explode(split(json_str1,
'
\\|
'
)) b
AS
name_tmp
group
by
,area
order
by
,area
