hviesql及sparksql数组及数组嵌套json的生成和解析

1、数组字符串
数据样例:["car1","car2","car3"]
sql实现：  
select
      id
      ,concat('["',(concat_ws('","',collect_list(car_name  ))),'"]')        as   car_names
from 
(
      select
          id
          ,car_name  
      from  table_name
      where  pt_d ='' 
)t
group by id
;


2、二维数组
数据样例
[{"age":"1","car_names":["car1","car2"]}
,{"age":"2","car_names":["car1","car2","car3"]}
]

sql实现：
select
    id
   ,concat('[',(concat_ws(',',collect_list(age_car_names))),']')                    as     age_car_names
from 
(
select
    id
  ,concat('{"age":"',age,'","car_names":',car_names,'}')    as  age_car_names
from 
(
  select
      id,age
      ,concat('["',(concat_ws('","',collect_list(car_name  ))),'"]')        as   car_names
  from table_name
  group by id,age
)t2
)t3
group by id
sparksql
select 
        id 
        ,concat('[',concat_ws(',',collect_set(to_json(struct(age,car_name)))),']')  as age_car_names
from table_name
group by 
    id


3、解析数组字符串
数据样例:["car1","car2","car3"]
解析后：
car_name
car1
car2
car3
select              
      explode(split(regexp_replace(car_names,'^\\[|\\]$|"',''),',')) as car_name                        
from table_name

4、解析二维数组
[{"age":"1","car_names":["car1","car2"]}
,{"age":"2","car_names":["car1","car2","car3"]}
]
解析后
age car_name
1    car1
1    car2
2    car1
2    car2
2    car3

select 
     age
     ,car_name
from 
(
    select    
           from_json(coalesce(age_car_names,'[""]'),'array<string>') as  age_car_names
    from table_name
) t11
lateral view explode(age_car_names) t12 as age_car_names_json
lateral view json_tuple(age_car_names_json,'age','car_name') t13 as age,car_name