I have this data stored in lookup.lov_data (JSON datatype):
[
{"value":"SEL", "label":"Selangor"},
{"value":"KUL", "label":"Kuala Lumpur"}
]
I want to get Selangor as the result. Whats the query?
I've tried:
SELECT lov_data->'$[*].label' AS state
FROM lookup
WHERE JSON_CONTAINS(lov_data->'$[*].value', JSON_ARRAY("SEL"));
If it's only single data not in array, I can simply use:
SELECT lov_data->'$.label' AS state
FROM lookup
WHERE lov_data->'$.value' = 'SEL'
At the moment (MySQL version 5.7.20) there's no way to extract an object from an array of objects, given the value of one of the object's keys. The function JSON_EXTRACT lets you select items in an array, but it is index based. Other than $[*], which select the whole array, you can only use $[i], where i must be an integer and represent the index of the desired item. If one could put a json object in $, like this: $['{"value": "SEL"}'], your problem was solved. Unfortunately, this is not possibile right now, and the only way to obtain the desired ouput, within a single select statement, at the moment, is:
SELECT lov_data->'$[0].label' AS state FROM lookup
WHERE JSON_CONTAINS(lov_data->'$[*].value', JSON_ARRAY("SEL"));
which probably is not what you need (i.e. you should know the desired item index in advance).
But you can store a function like this:
DROP FUNCTION IF EXISTS from_json_array;
DELIMITER //
CREATE FUNCTION from_json_array(jarray JSON, object_key VARCHAR(24), object_value VARCHAR(128), select_key VARCHAR(24))
RETURNS JSON
BEGIN
DECLARE jindex INT DEFAULT 0;
DECLARE jitem JSON;
WHILE(1)
DO
SET jitem = JSON_EXTRACT(jarray, CONCAT('$[', jindex, ']'));
IF jitem IS NULL THEN
RETURN NULL;
END IF;
IF JSON_CONTAINS(jitem, JSON_OBJECT(object_key, object_value)) THEN
RETURN JSON_EXTRACT(jitem, CONCAT('$.', select_key));
END IF;
SET jindex = jindex + 1;
END WHILE;
END//
DELIMITER ;
and use it this way:
SELECT from_json_array(
(SELECT JSON_EXTRACT(lov_data, '$[*]') FROM lookup WHERE
JSON_CONTAINS(lov_data, JSON_OBJECT('value', 'SEL'))),
'value', 'SEL', 'label') AS state;
