CODEWITHSUNDEEP
mysqljsonfunctionsyntaxcase
Joshua Croff
Joshua Croff

Reputation: 31

How Can I Create a MySQL Function to Check JSON Validity?

I'm fairly new to MySQL but I'd like to create a function to validate a JSON objects that are stored in my database tables.

I looked up information on creating a function, but must be missing something as I can't seem to get it to work. It doesn't seem like it would be overly complicated but perhaps I'm not using the appropriate syntax.

Here is my code: 

DELIMITER //

CREATE FUNCTION CHECKJSON( DB_NAME varchar(255), TABLE_NAME varchar(255),    JSON_COLUMN varchar(255)) 
RETURNS varchar(300)
BEGIN
    DECLARE notNullCount int; 
    DECLARE validJSONCount int; 
    DECLARE result varchar(300); 

    SET notNullCount = (SELECT count(*) FROM DB_NAME.TABLE_NAME WHERE JSON_COLUMN IS NOT NULL);
    set validJSONCount = (SELECT count(*) FROM DB_NAME.TABLE_NAME WHERE JSON_VALID(JSON_COLUMN) > 0); 

    CASE 
        WHEN (validJSONCount = notNullCount) THEN 
            SET result = CONCAT('VALID JSON COUNT: ', validJSONCount) 
        ELSE 
            SET result = CONCAT('INVALID JSON COUNT: ', (notNullCount - validJSONCount)) 
    END; 
    RETURN result; 
END //

DELIMITER ;

When I try to run this code, I get the following error message: "Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSE SET result = CONCAT('INVALID JSON COUNT: ', (notNullCount - validJSONC' at line 14"

Any thoughts on how I might improve this code? Thanks!

Upvotes: 3

Views: 5342

Answers (2)

Stalinko
Stalinko

Reputation: 3656

Since MySQL 5.7 you have a pretty and simple function for this:

JSON_VALID(value)

Returns 0 or 1 to indicate whether a value is valid JSON. Returns NULL if the argument is NULL.

https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-valid

Upvotes: 5

Ben Cummins
Ben Cummins

Reputation: 508

You're missing a couple of ; and to end the case it should be END CASE.

DELIMITER //

CREATE FUNCTION CHECKJSON( DB_NAME varchar(255), TABLE_NAME varchar(255),    JSON_COLUMN varchar(255)) 
RETURNS varchar(300)
BEGIN
    DECLARE notNullCount int; 
    DECLARE validJSONCount int; 
    DECLARE result varchar(300); 

    SET notNullCount = (SELECT count(*) FROM DB_NAME.TABLE_NAME WHERE JSON_COLUMN IS NOT NULL);
    set validJSONCount = (SELECT count(*) FROM DB_NAME.TABLE_NAME WHERE JSON_VALID(JSON_COLUMN) > 0); 

    CASE 
        WHEN (validJSONCount = notNullCount) THEN 
            SET result = CONCAT('VALID JSON COUNT: ', validJSONCount) ;
        ELSE 
            SET result = CONCAT('INVALID JSON COUNT: ', (notNullCount - validJSONCount)) ;
    END CASE; 
    RETURN result; 
END //

DELIMITER ;

Upvotes: 0

Related Questions