select id,concat_ws('||',collect_set(content)) as contents from ( select id, get_json_object(cl,'$.content') as content from ( select id, split(regexp_replace( regexp_extract (property_value ,'(\\[)(.*)(\\])',2) ,'},{','}|{'),'\\|') as property_value from properties where pt='20180627000000' and property_key ='specail' limit 1 ) b lateral view explode(property_value) tf as cl
) b group by id ;
结果 :
+-------------------+----------+
| id | contents | +-------------------+----------+ | 1110| 全部内容。||无其它特别约定。 |