MySQL JSON extract a single value by id

Question

I have a database with a JSON array in column extra_fields. I need to extract one value by id.

The JSONs looks something like this, though the number of objects is random for 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 is the target string"}
]

I can extract an array of ids with:

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

I can extract an array of values with:

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

I can extract a single value at the nth zero-ordered object position with:

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

But the problem is that there's a variable number of objects and I specifically need the value of object id = 11. JSON_EXTRACT doesn't seem to support filtering, as every variation of a JSONPATH seems to fail. $..[?(@.id=11)].value works as a JSONPATH, but this (and many variants) fails:

SELECT extra_fields->"$.[?(@.id=11)].value" FROM esqt7_k2_items; #FAILS

This is a migration project and the JSON stuff is just how Joomla chose to implement their extra fields. Kind of a pain if you ask me.

score 1 · Accepted Answer · answered Jul 26 '17 at 11:50

One option you can consider (be careful about 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 INTO `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 is the target string"}
    '>     ]
    '>   '),
    ->   ('
    '>     [
    '>       {"id": "1", "value": ""},
    '>       {"id": "9", "value": "ONE This is the target string"}
    '>     ]
    '>   '),
    ->   ('
    '>     [
    '>       {"id": "6", "value": ""},
    '>       {"id": "11", "value": "TWO This is the 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`
    -> FROM (
    ->   SELECT
    ->     `id`,
    ->     JSON_UNQUOTE(
    ->       JSON_EXTRACT(`extra_fields`,
    ->         CONCAT((SELECT
    ->           JSON_UNQUOTE(
    ->             JSON_SEARCH(`extra_fields` ->> '$[*].id', 'one', @`search_id`)
    ->           )
    ->                 FROM `esqt7_k2_items` `esqt7_k2_items_in`
    ->                 WHERE `esqt7_k2_items_out`.`id` = `esqt7_k2_items_in`.`id`
    ->                ), ".value"
    ->         )
    ->       )
    ->     ) `value`
    ->   FROM `esqt7_k2_items` `esqt7_k2_items_out`
    -> ) `der`
    -> WHERE `der`.`value` IS NOT NULL;
+----+-------------------------------+
| id | value                         |
+----+-------------------------------+
|  1 | This is the target string     |
|  3 | TWO This is the target string |
+----+-------------------------------+
2 rows in set (0.00 sec)

See db-fiddle.

It will take me a little while to test this, but the first part suggests that `extra_fields` needs to be type JSON with `id` as primary key for the query to work? — Slam, Jul 26 '17 at 18:01
@Slam: The table structure and data types of the columns were defined arbitrarily. You can modify and adjust as needed. — wchiquito, Jul 26 '17 at 19:27

score 1 · Answer 2 · answered Aug 25 '17 at 23:43

Simpliest way i found to get

the value of object id = 11

its by finding the right "index" of the needed object, doing a bit of PHP. Firstly we get all objects Ids, by your simple request:

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

then in PHP

$rightIndex = array_search($ids, '11');

and then you get the right object by passing this $rightIndex :

SELECT extra_fields->>"$[$rightIndex].value"  FROM esqt7_k2_items;

Simple as hell! The same thing you can do anywhere else, for example, when you need to UPDATE an object in array, or its value/values, etc.. That is, Cheers!

Unfortunately I needed to do this in MySQL not PHP. – Slam Aug 28 '17 at 23:30 — Slam, Aug 28 '17 at 23:30

MySQL JSON extract a single value by id

2 Answers2