Find in json by key in mysql

Question

I have a json and i want to extract the value of a key. I know the key name but not the location. My json is complex and would look like this in mysql:

set @jsonstr:='
{
    "glossary": {
        "title": "example glossary",
        "GlossDiv": {
            "title": "S",
            "GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
                    "SortAs": "SGML",
                    "GlossTerm": "Standard Generalized Markup Language",
                    "Acronym": "SGML",
                    "Abbrev": "ISO 8879:1986",
                    "GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
                        "GlossSeeAlso": ["GML", "XML"]
                    },
                    "GlossSee": "markup"
                }
            }
        }
    }
}
';

For example , I want to search for the key "Acronym". I want either the full path / or directly the value of it ["SGML"] so that i can process it further.

Im doing the below and its returning me null

select JSON_Search(@jsonstr,'all', '%Acronym%')

My requirements :

• There could be multiple "Acronym" in my json and i just want all of them.
• I dont have the value part , i just know the key
• My json is complex with nested objects and arrays

---

Edit

It worked, so i now tried with my updated json which looks like

    set @jsonstr:='
{
    "glossary": {
        "title": "example glossary",
        "GlossDiv": {
            "title": "S",
            "GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
                    "SortAs": "SGML",
                    "GlossTerm": "Standard Generalized Markup Language",
                    "Acronym": "SGML",
                    "Abbrev": "ISO 8879:1986",
                    "GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
                        "GlossSeeAlso": ["GML", "XML"],
                        "Acronym" : "another value"
                    },
                    "GlossSee": "markup"
                }
            }
        }
    }
}
';

So now the query

select JSON_EXTRACT(@jsonstr,'$**.Acronym')

returned

["SGML", "another value"]

But like this I would be unable to fetch the paths of various locations that have my targeted key.

Ques : If i could get a table back with the "key location" as the first column and their respective values in the second column.

Answer 1

I think you want this query:

SELECT JSON_EXTRACT(@jsonstr,'$**.Acronym')

JSON_Search looks for values in the JSON object. You know the name of the key you are looking for, so all you need is to use the path, which is explained at https://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html with examples at https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-search