Get the value of a property given the name of a key, within a JSON object

Question

I'm trying to get the value of a property, contained inside a JSON object, satisying the condition that the name of another property matches a specific value.

Let's say I've got the followig JSON object stored in a column:

{
"f2": [
    {"name":"f21","value":"foo_21"},
    {"name":"hit","value":"foo_hit"},
    {"name":"f22","value":"foo_22"}
]
}

And I'd like to get the value "foo_hit" because it's the value matching the given name "hit" of the JSON object the value "foo_hit" belongs to. Bear in mind that the matching JSON object {"name":"hit","value":"foo_hit"} could appear in whatever position within the array.

The expected value to get would be "foo_hit" (double quotation marks included).

To accomplish that, I've tried the following sql statements (based on some clues got from this post):

CREATE TABLE mytable (jsonstr JSON);

INSERT INTO mytable VALUES 
('{"f2": [{"name":"f21","value":"foo_21" }, 
          {"name":"hit","value":"foo_hit"}, 
          {"name":"f22","value":"foo_22" }]}');

SELECT JSON_EXTRACT(jsonstr,'$**.name') 
FROM mytable 
WHERE (JSON_EXTRACT(jsonstr,'$**.name')="hit");

But the SELECT statement produces an empty result.

Any clarification about how to accomplish the targeted task would be appreciated.

Answer 1

You can use JSON_TABLE to extract values from your table. This will ensure that your values are correctly linked to your names. Then it's enough to filter your values using the corresponding "value_" column.

SELECT tab.value_
FROM mytable, 
     JSON_TABLE(mytable.jsonstr->'$.f2',
                '$[*]' COLUMNS (name_  VARCHAR(30) PATH '$.name',
                                value_ VARCHAR(30) PATH '$.value')
               ) AS tab
WHERE tab.name_ = 'hit'

"Output":

value_
foo_hit

Check the demo here.