SQL中常用函数

COALESCE函数

• COALESCE(‘参数1’，‘参数2’，…,‘参数N’)

返回参数中必须是非NULL的值，要求必须包含一个非空的值。

字段类型为Jsonb

数据库以PG为例

从SQL(数据库为PG)中获取某特定字段或属性。

{
    "bankName": "上海浦东发展银行",
    "certNoMask": "321321**********3*",
    "cityName": "宿迁市",
    "custNameMask": "徐**",
    "mobileMask": "158****1003",
    "productName": "浦发银行京东PLUS联名卡",
    "productNo": "",
    "provinceName": "江苏",
    "secBankNo": "",
    "serialId": "FX_L1_000973_1662104787152Tj51b9g"
}

例如： SELECT
        COALESCE(response_data::json ->> 'custNameMask','开卡进行中') AS custNameMask,
        response_data::json ->> 'mobileMask' AS mobileMask,
        response_data::json ->> 'bankName' AS bankName,
        response_data::json ->> 'provinceName' AS provinceName,
        response_data::json ->> 'cityName' AS cityName,
        process_status AS status,
        cash_order AS cashback
        FROM
        <include refid="table_name"/>
        -- 对于上述字段:custNameMask|mobileMask|bankName|provinceName|cityName 等这些均对应json字符串中。

从JSON字符串中解析jsonarray，数据格式即就是

{
    "businessName": "xxx科技有限公司",
    "businessNo": "FX_L1_000973",
    "cardStatusList": [
        {
            "changeDate": "",
            "statusKey": "firstAudit",
            "statusValue": "0"
        },
        {
            "changeDate": "20220904",
            "statusKey": "approve",
            "statusValue": "2"
        },
        {
            "changeDate": "",
            "statusKey": "activate",
            "statusValue": "0"
        }
    ]
}

-- 可采用json_array_elements函数 来分别取出对应的a与b的值
SELECT 
json_array_elements(response_data::json -> 'cardStatusList') ->> 'statusKey' as status_name,
json_array_elements(response_data::json -> 'cardStatusList') ->> 'statusValue' as status_Val,
json_array_elements(response_data::json -> 'cardStatusList') ->> 'changeDate' as changeDate
from table1
WHERE id = 14;

• 查询结果