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;

you have an error in your code three times `source_updated_at,)` a comma too much , also ` ELSEIF email IS NOT NULL THEN`should be emailId, the rest looks ok to me, but i really would check if instructorId,ssoId or eamilId can be null — nbk, Apr 29 '23 at 16:13
Why have any code in this function? It always returns `-1` regardless of the input or the data it queries. — Bill Karwin, Apr 29 '23 at 17:01
I don't think this can be the function you are testing. I tested creating it on MySQL 8.0, but the extra comma characters make it throw a syntax error when I tried to create the function. I can't create it, so I can't execute it. — Bill Karwin, Apr 29 '23 at 17:03
@nbk corrected the error, also we are executing the query dependent on the null check only (it satisfies the business logic am trying to add) — Midhun Kumar, Apr 30 '23 at 05:23
@BillKarwin the code is not complete, at the end if there is no existing record to be fetched using sso,instructorid or email, i insert a new record and finally i need to return the complete user object as json. Hope this clarifies. Also, updated the coma issue. — Midhun Kumar, Apr 30 '23 at 05:25
The requirement is to update the record if its exists else create it and return the udpated or created user — Midhun Kumar, Apr 30 '23 at 05:46

score 0 · Answer 1 · answered Apr 30 '23 at 06:12

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).

Stored Function and dynamic where clause and json_object error fix

1 Answers1