MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (
  char_value VARCHAR(255),
  json_value JSON
);
INSERT INTO test (char_value)
WITH RECURSIVE cte (id) AS (
  SELECT 1 UNION ALL SELECT id + 1 FROM cte LIMIT 1000
)
SELECT CONCAT('{"key":', RAND() * 100, '}') FROM cte;
UPDATE test SET json_value = char_value;
SELECT char_value, json_value, CAST(json_value AS CHAR) FROM test LIMIT 10;

Records: 1000  Duplicates: 0  Warnings: 0



Rows matched: 1000  Changed: 1000  Warnings: 0




char_value
json_value
CAST(json_value AS CHAR)


{"key":36.91424218650371}
7b226b6579223a2033362e39313432343231383635303337317d
{"key": 36.91424218650371}

{"key":6.623920804470703}
7b226b6579223a20362e3632333932303830343437303730337d
{"key": 6.623920804470703}

{"key":22.376880536206887}
7b226b6579223a2032322e3337363838303533363230363838377d
{"key": 22.376880536206887}

{"key":92.01264334098683}
7b226b6579223a2039322e30313236343333343039383638347d
{"key": 92.01264334098684}

{"key":92.93257816967795}
7b226b6579223a2039322e39333235373831363936373739357d
{"key": 92.93257816967795}

{"key":88.62496389879377}
7b226b6579223a2038382e36323439363338393837393337377d
{"key": 88.62496389879377}

{"key":64.32708805829947}
7b226b6579223a2036342e33323730383830353832393934377d
{"key": 64.32708805829947}

{"key":55.76055166848055}
7b226b6579223a2035352e37363035353136363834383035357d
{"key": 55.76055166848055}

{"key":85.82149724086887}
7b226b6579223a2038352e38323134393732343038363838377d
{"key": 85.82149724086887}

{"key":61.825834272267144}
7b226b6579223a2036312e3832353833343237323236373134347d
{"key": 61.825834272267144}


-- фиксируем время начала
SET @start_time = CURRENT_TIMESTAMP(6);
-- выполняем поиск по JSON в текстовом поле
SELECT COUNT(*) FROM test WHERE char_value->"$.key" BETWEEN 10 AND 20;
-- смотрим затраченное время, в микросекундах
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, CURRENT_TIMESTAMP(6)) elapsed_time;


COUNT(*)


107




elapsed_time


3617


-- фиксируем время начала
SET @start_time = CURRENT_TIMESTAMP(6);
-- выполняем поиск по JSON в поле типа JSON
SELECT COUNT(*) FROM test WHERE json_value->"$.key" BETWEEN 10 AND 20;
-- смотрим затраченное время, в микросекундах
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, CURRENT_TIMESTAMP(6)) elapsed_time;


COUNT(*)


107




elapsed_time


1518

char_value	json_value	CAST(json_value AS CHAR)
{"key":36.91424218650371}	7b226b6579223a2033362e39313432343231383635303337317d	{"key": 36.91424218650371}
{"key":6.623920804470703}	7b226b6579223a20362e3632333932303830343437303730337d	{"key": 6.623920804470703}
{"key":22.376880536206887}	7b226b6579223a2032322e3337363838303533363230363838377d	{"key": 22.376880536206887}
{"key":92.01264334098683}	7b226b6579223a2039322e30313236343333343039383638347d	{"key": 92.01264334098684}
{"key":92.93257816967795}	7b226b6579223a2039322e39333235373831363936373739357d	{"key": 92.93257816967795}
{"key":88.62496389879377}	7b226b6579223a2038382e36323439363338393837393337377d	{"key": 88.62496389879377}
{"key":64.32708805829947}	7b226b6579223a2036342e33323730383830353832393934377d	{"key": 64.32708805829947}
{"key":55.76055166848055}	7b226b6579223a2035352e37363035353136363834383035357d	{"key": 55.76055166848055}
{"key":85.82149724086887}	7b226b6579223a2038352e38323134393732343038363838377d	{"key": 85.82149724086887}
{"key":61.825834272267144}	7b226b6579223a2036312e3832353833343237323236373134347d	{"key": 61.825834272267144}