MySQL JSON extract a single value by id -

i have database json array in column extra_fields. need extract 1 value id.

the jsons looks this, though number of objects random each row:

[ {"id":"1","value":"48768"}, {"id":"2","value" ["string","http:someurl"]}, {"id":"5","value":"somevalue"}, {"id":"6","value":""}, {"id":"8","value":"op-ed"}, {"id":"9","value":"8111,13498,15408"}, {"id":"10","value":"30"}, {"id":"11","value":"this target string"} ] 

i can extract array of ids with:

select extra_fields->>"$[*].id"  esqt7_k2_items; 

i can extract array of values with:

select extra_fields->>"$[*].value"  esqt7_k2_items; 

i can extract single value @ nth zero-ordered object position with:

select extra_fields->>"$[2].value"  esqt7_k2_items; 

but problem there's variable number of objects , need value of object id = 11. json_extract doesn't seem support filtering, every variation of jsonpath seems fail. $..[?(@.id=11)].value works jsonpath, (and many variants) fails:

select extra_fields->"$.[?(@.id=11)].value" esqt7_k2_items; #fails

this migration project , json stuff how joomla chose implement fields. kind of pain if ask me.

one option can consider (be careful performance issues):

mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.19    | +-----------+ 1 row in set (0.00 sec)  mysql> drop table if exists `esqt7_k2_items`; query ok, 0 rows affected (0.00 sec)  mysql> create table if not exists `esqt7_k2_items` (     ->   `id` bigint unsigned not null auto_increment primary key,     ->   `extra_fields` json not null     -> ); query ok, 0 rows affected (0.00 sec)  mysql> insert `esqt7_k2_items`     ->   (`extra_fields`)     -> values     ->   ('     '>     [     '>       {"id": "1", "value": "48768"},     '>       {"id": "2", "value": ["string","http:someurl"]},     '>       {"id": "5", "value": "somevalue"},     '>       {"id": "6", "value": ""},     '>       {"id": "8", "value": "op-ed"},     '>       {"id": "9", "value": "8111,13498,15408"},     '>       {"id": "10", "value": "30"},     '>       {"id": "11", "value": "this target string"}     '>     ]     '>   '),     ->   ('     '>     [     '>       {"id": "1", "value": ""},     '>       {"id": "9", "value": "one target string"}     '>     ]     '>   '),     ->   ('     '>     [     '>       {"id": "6", "value": ""},     '>       {"id": "11", "value": "two target string"}     '>     ]     '>   '); query ok, 3 rows affected (0.00 sec) records: 3  duplicates: 0  warnings: 0  mysql> set @`search_id` := '11'; query ok, 0 rows affected (0.00 sec)  mysql> select `der`.`id`, `der`.`value`     -> (     ->   select     ->     `id`,     ->     json_unquote(     ->       json_extract(`extra_fields`,     ->         concat((select     ->           json_unquote(     ->             json_search(`extra_fields` ->> '$[*].id', 'one', @`search_id`)     ->           )     ->                 `esqt7_k2_items` `esqt7_k2_items_in`     ->                 `esqt7_k2_items_out`.`id` = `esqt7_k2_items_in`.`id`     ->                ), ".value"     ->         )     ->       )     ->     ) `value`     ->   `esqt7_k2_items` `esqt7_k2_items_out`     -> ) `der`     -> `der`.`value` not null; +----+-------------------------------+ | id | value                         | +----+-------------------------------+ |  1 | target string     | |  3 | 2 target string | +----+-------------------------------+ 2 rows in set (0.00 sec) 

see db-fiddle.