对array数据处理
with tablea as
(
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition
union all
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition
union all
select ‘中间状态客群名’ as group_condition
)
select group_condition from tablea;
with tablea as
(
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition
union all
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition
union all
select ‘中间状态客群名’ as group_condition
)
select group_condition,get_json_object(group_condition,‘
.
c
o
n
.
f
d
′
)
a
s
a
r
r
f
r
o
m
t
a
b
l
e
a
w
h
e
r
e
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) as arr from tablea where get_json_object(group_condition,’
.
co
n
.
f
d
′
)
a
s
a
rr
f
ro
m
t
ab
l
e
a
w
h
ere
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) is not null
union all
select group_condition, get_json_object(group_condition,‘
.
c
o
n
.
s
u
b
.
f
d
′
)
a
s
a
r
r
f
r
o
m
t
a
b
l
e
a
w
h
e
r
e
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) as arr from tablea where get_json_object(group_condition,’
.
co
n
.
s
u
b
.
f
d
′
)
a
s
a
rr
f
ro
m
t
ab
l
e
a
w
h
ere
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) is not null;
with tablea as
(
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition
union all
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition
union all
select ‘中间状态客群名’ as group_condition
)
select group_condition,get_json_object(group_condition,‘
.
c
o
n
.
f
d
′
)
a
s
a
r
r
,
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
f
d
′
)
a
s
a
rr
,
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’),‘”’,‘’),‘\[|\]’,‘’)
,c1
from tablea
–where get_json_object(group_condition,‘
.
c
o
n
.
f
d
′
)
i
s
n
o
t
n
u
l
l
l
a
t
e
r
a
l
v
i
e
w
e
x
p
l
o
d
e
(
s
p
l
i
t
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) is not null lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
f
d
′
)
i
s
n
o
t
n
u
lll
a
t
er
a
l
v
i
e
w
e
x
pl
o
d
e
(
s
pl
i
t
(
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) ,‘”’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1
union all
select group_condition,get_json_object(group_condition,‘
.
c
o
n
.
s
u
b
.
f
d
′
)
a
s
a
r
r
,
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
s
u
b
.
f
d
′
)
a
s
a
rr
,
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’),‘”’,‘’),‘\[|\]’,‘’)
,c1
from tablea
–where get_json_object(group_condition,‘
.
c
o
n
.
s
u
b
.
f
d
′
)
i
s
n
o
t
n
u
l
l
l
a
t
e
r
a
l
v
i
e
w
e
x
p
l
o
d
e
(
s
p
l
i
t
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) is not null lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
s
u
b
.
f
d
′
)
i
s
n
o
t
n
u
lll
a
t
er
a
l
v
i
e
w
e
x
pl
o
d
e
(
s
pl
i
t
(
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) ,‘”’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1;
with tablea as
(
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition
union all
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition
union all
select ‘中间状态客群名’ as group_condition
) ,
asd as (select group_condition,get_json_object(group_condition,‘
.
c
o
n
.
f
d
′
)
a
s
a
r
r
,
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
f
d
′
)
a
s
a
rr
,
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’),‘”’,‘’),‘\[|\]’,‘’)
,c1
from tablea
–where get_json_object(group_condition,‘
.
c
o
n
.
f
d
′
)
i
s
n
o
t
n
u
l
l
l
a
t
e
r
a
l
v
i
e
w
e
x
p
l
o
d
e
(
s
p
l
i
t
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) is not null lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
f
d
′
)
i
s
n
o
t
n
u
lll
a
t
er
a
l
v
i
e
w
e
x
pl
o
d
e
(
s
pl
i
t
(
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) ,‘”’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1
union all
select group_condition,get_json_object(group_condition,‘
.
c
o
n
.
s
u
b
.
f
d
′
)
a
s
a
r
r
,
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
s
u
b
.
f
d
′
)
a
s
a
rr
,
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’),‘”’,‘’),‘\[|\]’,‘’)
,c1
from tablea
–where get_json_object(group_condition,‘
.
c
o
n
.
s
u
b
.
f
d
′
)
i
s
n
o
t
n
u
l
l
l
a
t
e
r
a
l
v
i
e
w
e
x
p
l
o
d
e
(
s
p
l
i
t
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) is not null lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
s
u
b
.
f
d
′
)
i
s
n
o
t
n
u
lll
a
t
er
a
l
v
i
e
w
e
x
pl
o
d
e
(
s
pl
i
t
(
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) ,‘”’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1)
select * from asd;
with tablea as
(
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“fd”:“client_name”,“cmp”:7,“val”:“陈洋勇”,“desc”:“客户姓名 包含 陈洋勇”,“c_has_operator”:“3”},{“op”:1,“fd”:“client_age”,“cmp”:4,“val”:“29”,“desc”:“年龄 = 29岁”,“c_has_operator”:“1”}]}’ as group_condition
union all
select ‘{“ver”:“2.0”,“con”:[{“op”:1,“sub”:[{“op”:1,“fd”:“client_sex”,“cmp”:4,“val”:“0”,“desc”:“客户性别 = 男”,“c_has_operator”:“0”},{“op”:“0”,“fd”:“client_sex”,“cmp”:4,“val”:“1”,“desc”:“客户性别 = 女”,“c_has_operator”:“0”}]}]}’ as group_condition
union all
select ‘中间状态客群名’ as group_condition
) ,
asd as (select group_condition,get_json_object(group_condition,‘
.
c
o
n
.
f
d
′
)
a
s
a
r
r
,
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
f
d
′
)
a
s
a
rr
,
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’),‘”’,‘’),‘\[|\]’,‘’)
,c1
from tablea
–where get_json_object(group_condition,‘
.
c
o
n
.
f
d
′
)
i
s
n
o
t
n
u
l
l
l
a
t
e
r
a
l
v
i
e
w
e
x
p
l
o
d
e
(
s
p
l
i
t
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) is not null lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
f
d
′
)
i
s
n
o
t
n
u
lll
a
t
er
a
l
v
i
e
w
e
x
pl
o
d
e
(
s
pl
i
t
(
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.fd’) ,‘”’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1
union all
select group_condition,get_json_object(group_condition,‘
.
c
o
n
.
s
u
b
.
f
d
′
)
a
s
a
r
r
,
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) as arr ,regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
s
u
b
.
f
d
′
)
a
s
a
rr
,
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’),‘”’,‘’),‘\[|\]’,‘’)
,c1
from tablea
–where get_json_object(group_condition,‘
.
c
o
n
.
s
u
b
.
f
d
′
)
i
s
n
o
t
n
u
l
l
l
a
t
e
r
a
l
v
i
e
w
e
x
p
l
o
d
e
(
s
p
l
i
t
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
r
e
g
e
x
p
r
e
p
l
a
c
e
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
g
r
o
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) is not null lateral view explode(split(regexp_replace(regexp_replace(get_json_object(group_condition,’
.
co
n
.
s
u
b
.
f
d
′
)
i
s
n
o
t
n
u
lll
a
t
er
a
l
v
i
e
w
e
x
pl
o
d
e
(
s
pl
i
t
(
re
g
e
x
p
r
e
pl
a
ce
(
re
g
e
x
p
r
e
pl
a
ce
(
g
e
t
j
so
n
o
bj
ec
t
(
g
ro
u
p
c
o
n
d
i
t
i
o
n
,
′
.con.sub.fd’) ,‘”’,‘’),‘\[|\]’,‘’) ,‘,’)) b as c1)
select c1,count(1) from asd group by c1;
