Extract data from json inside mysql field

I have wrapped this into a stored function for those constrained to MySQL <5.7.7:

CREATE FUNCTION `json_extract_string`(
    p_json text,
    p_key text
) RETURNS varchar(40) CHARSET latin1
BEGIN
    SET @pattern = CONCAT('"', p_key, '":"');
    SET @start_i = LOCATE(@pattern, p_json) + CHAR_LENGTH(@pattern);
    if @start_i = CHAR_LENGTH(@pattern) then
        SET @end_i = 0;
    else
        SET @end_i = LOCATE('"', p_json, @start_i) - @start_i;
    end if;
    RETURN SUBSTR(p_json, @start_i, @end_i);
END

Note this only works with string values but is a bit more robust than @DmitryK's answer, in that it returns an empty string if the key is not found and the key can be anywhere in the JSON string.

MySQL has got support for JSON in version 5.7.7 http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/ You will be able to use the jsn_extract function to efficiently parse your JSON string.

If you have an older version and you want to solve it purely in mysql then I am afraid you have to treat it as a string and cut the value out of it (just normal string functions or use regular expressions) This is not elegant but it will work

http://sqlfiddle.com/#!9/97cfd/14

SELECT
  DISTINCT(substring(jsonfield, locate('"city":',jsonfield)+8,
     locate('","', jsonfield, locate('"city":',jsonfield))-locate('"city":',jsonfield)-8)
  )
FROM
  ForgeRock