mysql - 如何根据配置单元中列表的字段匹配从 json_object 中选取值

Question

我正在使用 json_object，其中对于不同的产品类别，我们有不同的颜色变量用于前 - 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"]}

Answer 1

可以使用 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;

OK
1       Black
2       single
3       Multicolor

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