By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE FUNCTION json_extract_c ( details TEXT,
required_field VARCHAR (255) )
RETURNS text CHARSET utf8mb4
NO SQL
DETERMINISTIC
BEGIN
SET @json := details;
SET @field := required_field;
SET @output := '[';
WHILE LOCATE(@field, @json) DO
SET @json := SUBSTRING(@json FROM LOCATE(@field, @json));
SET @json := SUBSTRING(@json FROM 1 + LOCATE(':', @json));
SET @json := TRIM(@json);
SET @value := LEFT(@json, LEAST(LOCATE(' ', CONCAT(@json, ' ')),
LOCATE(',', CONCAT(@json, ',')),
LOCATE(']', CONCAT(@json, ']')),
LOCATE('}', CONCAT(@json, '}'))) - 1);
SET @json := SUBSTRING(@json FROM 1 + LENGTH(@value));
SET @output := CONCAT(@output, @value, ',');
-- or SET @output := CONCAT(@output, TRIM(BOTH '"' FROM @value), ',');
END WHILE;
RETURN CONCAT(TRIM(TRAILING ',' FROM @output), ']');
END
SET @details := '{"map":[{"text":"a"},{"text":"b"},{"text":"c"},{"text":"d"},{"text":"e"},{"text":"f"}]}';
SET @field := '"text"';
SELECT @details, @field;
SELECT json_extract_c(@details, @field);
| @details | @field |
|---|---|
| {"map":[{"text":"a"},{"text":"b"},{"text":"c"},{"text":"d"},{"text":"e"},{"text":"f"}]} | "text" |
| json_extract_c(@details, @field) |
|---|
| ["a","b","c","d","e","f"] |
SET @details := '{"map":[{"hello":"a","text":"a","hello":"a"},{"text":"b","hello":"b"},{"text":"c","hello":"c"},{"text":"d","hello":"d"},{"text":"e","hello":"e"},{"text":"f","hello":"f"}]}';
SET @field := '"text"';
SELECT @details, @field;
SELECT json_extract_c(@details, @field);
| @details | @field |
|---|---|
| {"map":[{"hello":"a","text":"a","hello":"a"},{"text":"b","hello":"b"},{"text":"c","hello":"c"},{"text":"d","hello":"d"},{"text":"e","hello":"e"},{"text":"f","hello":"f"}]} | "text" |
| json_extract_c(@details, @field) |
|---|
| ["a","b","c","d","e","f"] |