sqljsondatabasepostgresqljsonb

any way to fix" invalid input syntax for type json" in postgresql?


I have created a function of "login" to return me the user if match found with username or email as JSON and parameter is JSON, argument is json , all my other function work but this one is giving me error of " invalid input syntax for type json". I have no idea why, I have checked json validation, and all other solution to fix it but failed, my entire day has gone to hell because of it, please help me to fix it. THis is my code:

    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
    -- check if all required variables are available or not
    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;

This is my input format:

SELECT * FROM login('
                   {"username":"raihan123","email":"[email protected]"}
                   ');

This is the error I am getting:

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

I am tired, and I will be grateful if you can help me to fix it. Thanks in advance!!!

And if anyone want to know my table have the data and I can pull the data using select command and have checked it

SELECT * FROM users WHERE username='raihan123' OR email='[email protected]'

Please help me to fix it, My ENTIRE DAY IS FINISHED JUST TO FIND A WAY TO FIX IT


Solution

  • You didn't share the DDL for your table, but this is the issue:

    email INTO _user

    Your column email is most likely a VARCHAR, your variable _user is declared a JSONB. That's not going to work.

    The other issue is username, because where should that go? You don't have a variable for that one, unless it should be part of the json object _user.

    My bett is that you need this solution:

    _user VARCHAR; -- not jsonb, and you don't need NULL because it's already NULL

    And you still have to make a decision about how to handle username.

    Note: Please refrain from using all caps as it may convey frustration due to a mistake. Remember, remaining calm helps to minimize errors and encourages more assistance.