Skip to content

Instantly share code, notes, and snippets.

@justinkelly
Last active February 4, 2018 12:27
Show Gist options
  • Save justinkelly/aa0276bc492ff5b948e0f29f7fc744ca to your computer and use it in GitHub Desktop.
Save justinkelly/aa0276bc492ff5b948e0f29f7fc744ca to your computer and use it in GitHub Desktop.
json_extract_c for mysql 5.5
DELIMITER $$
DROP FUNCTION IF EXISTS `json_extract_c`$$
CREATE FUNCTION `json_extract_c`(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE search_term TEXT;
SET details = SUBSTRING_INDEX(details, "{", -1);
SET details = SUBSTRING_INDEX(details, "}", 1);
SET search_term = CONCAT('"', SUBSTRING_INDEX(required_field,'$.', - 1), '"');
IF INSTR(details, search_term) > 0 THEN
RETURN TRIM(
BOTH '"' FROM SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
details,
search_term,
- 1
),
',"',
1
),
':',
-1
)
);
ELSE
RETURN NULL;
END IF;
END$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment