PostgreSQL中login函数报invalid input syntax for type json错误求助

我创建了一个login函数，接收JSONB类型参数，匹配username或email后返回JSONB格式的用户信息。其他函数运行正常，但该函数始终报“invalid input syntax for type json”错误。已验证JSON格式有效性，尝试多种解决方案均失败，耗费一整天仍未解决。通过SELECT语句能正常查询到对应用户数据，恳请帮忙排查修复。

函数代码 #

DROP FUNCTION IF EXISTS login;
CREATE OR REPLACE FUNCTION login(data JSONB)
RETURNS JSONB AS $$
DECLARE
    _user JSONB = NULL::JSONB;
    _username VARCHAR = coalesce((data->>'username')::varchar, NULL);
    _email VARCHAR = coalesce((data->>'email')::varchar, NULL);
BEGIN
    -- 检查必填参数是否存在
    IF _username IS NULL OR _email IS NULL THEN
        RETURN JSON_BUILD_OBJECT(
            'status', 'failed',
            CASE WHEN _username IS NULL THEN 'username' ELSE 'email' END, 'required'
        );
    END IF;
    
    SELECT username, email INTO _user
    FROM users
    WHERE username = _username OR email = _email;
    
    RETURN JSON_BUILD_OBJECT(
        'status', CASE WHEN _user IS NULL THEN 'failed' ELSE 'success' END,
        'user', _user
    );
END;
$$ LANGUAGE plpgsql;

调用语句 #

SELECT * FROM login('
                   {"username":"raihan123","email":"raihan123@gmail.com"}
                   ');

错误信息 #

ERROR:  Token "raihan123" is invalid.invalid input syntax for type json 

ERROR:  invalid input syntax for type json
SQL state: 22P02
Detail: Token "raihan123" is invalid.
Context: JSON data, line 1: raihan123
PL/pgSQL function login(jsonb) line 15 at SQL statement

验证查询（可正常返回用户数据） #

SELECT * FROM users WHERE username='raihan123' OR email='raihan123@gmail.com'

问题原因与修复方案 #

核心问题 #

错误出现在SELECT username, email INTO _user这一行：你试图将两个独立的VARCHAR字段直接赋值给JSONB类型变量_user，PostgreSQL会尝试将这两个字段解析为JSON，但这种写法不符合JSON语法规则，导致抛出语法错误。

修复方法 #

有两种可靠的修复方式：

方式1：使用TO_JSONB()转换查询结果

将查询到的用户行（或指定字段）转换为JSONB类型：

-- 转换整行用户数据为JSONB
SELECT TO_JSONB(u) INTO _user
FROM users u
WHERE username = _username OR email = _email;

-- 仅转换username和email字段为JSONB
SELECT TO_JSONB(row(u.username, u.email)) INTO _user
FROM users u
WHERE username = _username OR email = _email;

方式2：手动构建JSON对象

使用JSON_BUILD_OBJECT()明确构造包含指定字段的JSON：

SELECT JSON_BUILD_OBJECT('username', username, 'email', email) INTO _user
FROM users
WHERE username = _username OR email = _email;

额外优化点 #

原代码中coalesce((data->>'username')::varchar, NULL)可以简化为data->>'username'，因为->>操作符本身返回的就是VARCHAR类型，无需额外强制转换。

内容的提问来源于stack exchange，提问作者Raihan_Alam