mysql - 如何基于Hive中列表的字段匹配从json_object中选择值

我正在使用json_object，其中对于不同的产品类别，我们为ex- kdt_color，fcy_color等具有不同的颜色变量（请检查以下内容）

如何为每种产品选择合适的color_variable以从json_object中提取颜色值。有超过100个垂直行业，因此不能在此处使用。

{"ctg_ideal_for":["Women"],"ctg_fabric":["Chiffon"],"ctg_design_style":["Umbrella Burqa"],"aba_color":["Black"],"aba_sleeve":["Full Sleeves"],"aba_with_hijab":[true]}

{"blz_color":["single"],"blz_size":["34\"36\"38\"40\"42"],"blz_sleeve_type":["Full Sleeves"],"ctg_ideal_for":["Men"],"ctg_fabric":["Imported"],"ctg_design_style":["Plaid Blazer"]}

{"color":["Multicolor"],"material":["PU"],"ideal_for":["Women"],"closure":["Zipper"],"bpk_style_code":["RMMY2418"]}

最佳答案

可以使用regexp_extract从JSON字符串中提取color":["SomeColor，如下所示：

select nbr,  regexp_extract(json, 'color":\\["([A-Za-z]*)',1) as color
from
(
select 1 as nbr,  '{"ctg_ideal_for":["Women"],"ctg_fabric":["Chiffon"],"ctg_design_style":["Umbrella Burqa"],"aba_color":["Black"],"aba_sleeve":["Full Sleeves"],"aba_with_hijab":[true]}' as json union all
select 2 as nbr,  '{"blz_color":["single"],"blz_size":["34\"36\"38\"40\"42"],"blz_sleeve_type":["Full Sleeves"],"ctg_ideal_for":["Men"],"ctg_fabric":["Imported"],"ctg_design_style":["Plaid Blazer"]}' as json union all
select 3 as nbr,  '{"color":["Multicolor"],"material":["PU"],"ideal_for":["Women"],"closure":["Zipper"],"bpk_style_code":["RMMY2418"]}' as json
)s;

SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 290 msec
OK
1       Black
2       single
3       Multicolor
Time taken: 41.775 seconds, Fetched: 3 row(s)

您还可以使用RegexSerDe在表DDL中定义正则表达式列。

关于mysql - 如何基于Hive中列表的字段匹配从json_object中选择值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51441364/