I'm trying to find a way to search a JSON object and get a specific key, but search for another key.

This is a sample schema:

CREATE TABLE `fields` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `options` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `fields` (label, options) VALUES ( 'My Field', '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, {"text": "Synthetic (Old Type - Hard)", "value": "3"}, {"text": "Gravel", "value": "5"}, {"text": "Clay", "value": "6"}, {"text": "Sand", "value": "7"}, {"text": "Grass/Synthetic Mix", "value": "8"}]' );

Database Fiddle: https://www.db-fiddle.com/f/npPgVqh7fJL2JweGJ5LWXE/1

So what I want is to select the string "Grass" fromoptionsby providing the ID1.

But there seems to be no way to do this. I can get grass by doing this:

select JSON_EXTRACT(`options`, '$[0].text') from `fields`; // "Grass"

But this requires knowing the index of the array

I can partially get the index of the array like this:

select JSON_SEARCH(`options`, 'one', '1') from `fields`; // "$[0].value"

And get the index itself by doing something really scary like this:

select REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', '') from `fields`; // 0

Even achieving what I want by doing really scary things like this:

select JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', ''), '].text')) from `fields`; // "Grass"

But there must be a better way, right?

Database Fiddle: https://www.db-fiddle.com/f/npPgVqh7fJL2JweGJ5LWXE/1

select field_options.* from fields cross join json_table(fields.options, '$[*]' columns( text text path '$.text', value text path '$.value' ) ) as field_options where field_options.value = 1; +-------+-------+ | text | value | +-------+-------+ | Grass | 1 | +-------+-------+

SELECT * FROM field_options WHERE value = '1';