Stored Function and dynamic where clause and json_object error fix

Question

I have defined a stored function in mysql 8.0: But whenever I try to hit the function with the below mentioned call, its throwing an error saying, #1582 - Incorrect parameter count in the call to native function 'JSON_OBJECT'. How to resolve it? Also, how can I define the select query using prepared statement with dynamic where clause, where both the key and value of the where condition is dynamic?

BEGIN
    DECLARE ssoId VARCHAR(255) DEFAULT NULL;
    DECLARE emailId VARCHAR(255) DEFAULT NULL;
    DECLARE instructorId VARCHAR(255) DEFAULT NULL;
    DECLARE firstName VARCHAR(255) DEFAULT NULL;
    DECLARE lastName VARCHAR(255) DEFAULT NULL;
    DECLARE createdAt TIMESTAMP DEFAULT NULL;
    DECLARE updatedAt TIMESTAMP DEFAULT NULL;
    DECLARE storedUser JSON DEFAULT NULL;
    
    SET ssoId = JSON_EXTRACT(user,'$.ssoId');
    SET emailId = JSON_EXTRACT(user,'$.email');
    SET firstName = JSON_EXTRACT(user,'$.firstName');
    SET lastName = JSON_EXTRACT(user,'$.lastName');
    SET createdAt = JSON_EXTRACT(user,'$.createdAt');
    SET updatedAt = JSON_EXTRACT(user,'$.updatedAt');
    IF JSON_EXTRACT(user,'$.instructorId') IS NOT NULL THEN
        SET instructorId = JSON_EXTRACT(user,'$.instructorId');
    ELSE 
        SET instructorId = JSON_EXTRACT(user,'$.instructorStudentId');
    END IF;
    
    IF ssoId IS NOT NULL THEN
        SELECT JSON_OBJECT(
            "id", id,
            "sso_id", sso_id,
            "email", email,
            "instructor_id", instructor_id,
            "first_name", first_name,
            "last_name", last_name,
            "source_created_at", source_created_at,
            "source_updated_at", source_updated_at)
        INTO storedUser FROM datahub.users WHERE sso_id = ssoId;
    ELSEIF instructorId IS NOT NULL THEN
        SELECT JSON_OBJECT(
            "id", id,
            "sso_id", sso_id,
            "email", email,
            "instructor_id", instructor_id,
            "first_name", first_name,
            "last_name", last_name,
            "source_created_at", source_created_at,
            "source_updated_at", source_updated_at)
        INTO storedUser FROM datahub.users WHERE instructor_id = instructorId;
    ELSEIF emailId IS NOT NULL THEN
        SELECT JSON_OBJECT(
            "id", id,
            "sso_id", sso_id,
            "email", email,
            "instructor_id", instructor_id,
            "first_name", first_name,
            "last_name", last_name,
            "source_created_at", source_created_at,
            "source_updated_at", source_updated_at)
        INTO storedUser FROM datahub.users WHERE email = emailId;
    END IF; 
    RETURN -1;
END;

Answer 1

I tested your function, but I don't see the error you described.

I do see another problem.

The result of JSON_EXTRACT() is a JSON scalar, not a string. So you would see double-quotes around it, like any JSON scalar string.

mysql> set @j = '{"key": "value"}';

mysql> select json_extract(@j, '$.key');
+---------------------------+
| json_extract(@j, '$.key') |
+---------------------------+
| "value"                   |
+---------------------------+

That naturally won't match any of the values in your table, because I assume they don't include double-quote characters.

You can get the string value by using JSON_UNQUOTE():

mysql> select json_unquote(json_extract(@j, '$.key'));
+-----------------------------------------+
| json_unquote(json_extract(@j, '$.key')) |
+-----------------------------------------+
| value                                   |
+-----------------------------------------+

You'll have to do this on each of the lines you extract JSON fields.

You asked how to run a dynamic SQL query. You can't do this in a MySQL stored function, because stored functions may be executed from prepared queries themselves.

https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html says:

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).