Hungry Machine Blog

CREATE FUNCTION JSON(`json` TEXT, `search_key` VARCHAR(255)) RETURNS TEXT DETERMINISTIC BEGIN

  DECLARE i INT DEFAULT 1;
  DECLARE json_length INT DEFAULT LENGTH(json);
  DECLARE state ENUM('reading_key','done_reading_key','reading_string', 'reading_array');
  DECLARE tmp_key TEXT;
  DECLARE tmp_value TEXT;
  DECLARE current_char VARCHAR(1);

  WHILE i <= json_length DO
    SET current_char = SUBSTRING(json,i,1);

    IF state = 'reading_key' THEN
      IF current_char = '"' THEN
        SET state = 'done_reading_key';
      ELSE
        SET tmp_key = CONCAT(tmp_key, current_char);
      END IF;
    ELSEIF state = 'done_reading_key' THEN
      IF current_char = '"' THEN
        SET state = 'reading_string';
      ELSEIF current_char = '[' THEN
        SET state = 'reading_array';
      END IF;
    ELSEIF state = 'reading_string' OR state = 'reading_array' THEN
      IF current_char = '\\' THEN
        SET i = i + 1;
        SET tmp_value = CONCAT(tmp_value, SUBSTRING(json,i,1));
      ELSEIF (state = 'reading_string' AND current_char = '"') OR (state = 'reading_array' AND current_char = ']') THEN
        IF search_key = tmp_key THEN
          RETURN tmp_value;
        ELSE
          SET state = NULL;
        END IF;
      ELSE
        SET tmp_value = CONCAT(tmp_value, current_char);
      END IF;
    ELSE 
      IF current_char='"' THEN
        SET state = 'reading_key';
        SET tmp_key = '';
        SET tmp_value = '';
      END IF;
    END IF;

    SET i = i + 1;
  END WHILE;

  RETURN NULL;
END

Examples

Here's a few examples of how it can be used:

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key1');
# returns 'val"ue1'

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key2');
# returns 'value2'

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key3');
# returns '"array1","array2"'

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key4');
# returns 'value4'

SELECT JSON('{"key1":"val\\"ue1","key2":"value2","key3":["array1","array2"],"key4":"value4"}', 'key5');
# returns NULL

Notes:

If you're trying to run this in the MySQL console, you'll need to set the DELIMITER to be something other than a semi-colon. Before executing the above, run "DELIMITER $$" and after executing it, run "$$" and then "DELIMITER ;" to set your delimiter back to semi-colon.

If you're trying to run this in a Rails migration, don't forget to escape the back-slashes (i.e. '\\' should become '\\\\')

Although this handles several simple use cases of extracting JSON-encoded data, it is by no means comprehensive. There are many JSON-encoded structures that this will not work on. This will not work correctly with nested arrays, or with named hashes.

The performance of this is pretty slow. A better approach would be to create a UDF that plugs into MySQL. Here's a UDF to encode JSON data, but not decode: http://www.mysqludf.org/lib_mysqludf_json/index.php

Faster Implementation:

CREATE FUNCTION JSON_FAST(`json` TEXT, `search_key` VARCHAR(255)) RETURNS TEXT DETERMINISTIC BEGIN
  IF INSTR(json, CONCAT('"', search_key, '":"')) THEN
    RETURN SUBSTRING_INDEX(SUBSTRING(json, INSTR(json, CONCAT('"', search_key, '":"')) +
           LENGTH(search_key) + 4), '"', 1);
  ELSEIF INSTR(json, CONCAT('"', search_key, '": "')) THEN
    RETURN SUBSTRING_INDEX(SUBSTRING(json, INSTR(json, CONCAT('"', search_key, '": "')) +
           LENGTH(search_key) + 5), '"', 1);
  ELSE
    RETURN NULL;
  END IF;
END

SELECT JSON('{"key":"value \"plus quotes\""}', 'key');
# returns 'value "plus quotes"'
SELECT JSON_FAST('{"key":"value \"plus quotes\""}', 'key');
# returns 'value \'

SELECT JSON('{"key":["value1","value2"]}', 'key');
# returns '"value1","value2"'
SELECT JSON_FAST('{"key":["value1","value2"]}', 'key');
# returns NULL