1、如果只有一级json,可以用A->'$.B'的方式提取
等同于json_extract(A,'$.B')
例如:
select claim_payment from claim where display_id='HAS62140122-B-00007MA.CL00000012'
select json_extract(claim_payment,'$.bank_name') from claim where display_id='HAS62140122-B-00007MA.CL00000012'
select claim_payment->'$.bank_name' from claim where display_id='HAS62140122-B-00007MA.CL00000012'
2、可以用json_unquote()去掉引号
例如:
select json_unquote(json_extract(claim_payment,'$.bank_name')) from claim where display_id='HAS62140122-B-00007MA.CL00000012'
3、如果是json数组,
A->'$.B'->'$.C'这样的方式就不行了,反正我试了就是取不到,报语法错误,可以用下面这种方式
json_extract(json_extract(A,'$.B'),'$.C')
select claim_form from claim where display_id='HAS62140122-B-00007MA.CL00000012'
select json_extract(json_extract(claim_form,'$.insured'),'$.full_name') from claim where display_id='HAS62140122-B-00007MA.CL00000012'
在用 json_unquote去掉引号
select json_unquote(json_extract(json_extract(claim_form,'$.insured'),'$.full_name')) from claim where display_id='HAS62140122-B-00007MA.CL00000012'