CODEWITHSUNDEEP
mysqljson
Coisox
Coisox

Reputation: 1104

MySQL JSON datatype

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'

Upvotes: 1

Views: 90

Answers (1)

p-a-o-l-o
p-a-o-l-o

Reputation: 10047

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;

Upvotes: 1

Related Questions