0

I have a JSON type field in mysql, and want to extract a specific field value:

{
    "doe": [
        {
            "firstname": "john",
            "age": 30,
            "married_to": "jane",
        },
        {
            "firstname": "jane",
            "age": 28,
            "married_to": "john",
        }
    ]
}

I want to select the age field WHERE firstname = 'jane'.

Problem: as the string jane occurs multiple times in both elements, I cannot simply use a JSON_SEARCH for jane to find the desired element:

SELECT JSON_SEARCH(JSON_EXTRACT(json, '$.doe'), 'one', 'jane') FROM persons; //this would find "$[0].married_to"

But how? And moreover: is my approach correct to find the array index first, and then to extract the age field based on it?

Update As I cannot post an answer, the following works:

set @json = '{"doe":[{"firstname": "john", "age": 30},{"firstname": "jane","age": 28}]}';

SELECT j.age
FROM JSON_TABLE(
        JSON_EXTRACT(@json, '$.doe'),
        '$[*]' COLUMNS (
                 firstname VARCHAR(10) PATH '$.firstname',
                 age int PATH '$.age'
         )
     ) j
WHERE j.firstname = 'jane';

The key part here was to combine JSON_EXTRACT with JSON_TABLE, which is not part of the answer in the linked 'duplicate' question in any way! So I still think this is relevant and not a duplicate.

membersound
  • 81,582
  • 193
  • 585
  • 1,120
  • 2
    This looks like a v.bad design, on at least 2 levels. The whole idea of json in a relational database makes little sense to me – RiggsFolly Nov 03 '21 at 16:07
  • The linked question addresses exactly what you are asking for: search for a value for a specific key ("firstname" in your case), and then get another value from another field ("age") in the same matched array element. – Bill Karwin Nov 03 '21 at 17:04

0 Answers0