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.

%Acronym% is a invalid path expression..you might want to read the manual again https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html... MySQL's JSON functions are to inmature to parse out multiple `Acronym` elements out the JSON structure specially when the json elements have random unknown key array elements or undefined array length... there isn't a magic JSON_TO_ROWS function — Raymond Nijland, Feb 16 '18 at 16:15
Looks like JSON_Search looks for values, not keys. So, for instance, `JSON_Search(@jsonstr,'all', 'SGML')` works, but your example doesn't, because `Acronym` is a key name, not a value. — Chris Lear, Feb 16 '18 at 16:22
`JSON_EXTRACT(@jsonstr,'$**.Acronym')` is closer to what you're trying to get hold of — Chris Lear, Feb 16 '18 at 16:24
What @RaymondNijland says is not correct, as the documentation shows. %Acronym% is a valid expression as an argument to JSON_Search, and MySQL's JSON functions appear to be perfectly well suited to this requirement without a magic JSON_TO_ROWS function. — Chris Lear, Feb 16 '18 at 16:33
"What RaymondNijland says is not correct, as the documentation shows. %Acronym% is a valid expression as an argument to JSON_Search" Well what you say is not correct @ChrisLear "To specify a literal % or _ character in the search string, precede it by the escape character" should have been `\%Acronym\%` — Raymond Nijland, Feb 16 '18 at 17:04
But he didn't want a literal % or _. He wanted to use % as a wildcard. Didn't he? — Chris Lear, Feb 16 '18 at 17:06
ah down votes... I wonder how "how to get element by id in jquery" like questions get 223456 up votes as if there was some research involved. I posted this question after trying it ,maybe I missed few things. But atleast I'm not the "don't know get element by id question" — ankur, Feb 16 '18 at 19:00

Chris Lear · Accepted Answer · 2018-02-16T16:38:38.750

7

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

edited Feb 16 '18 at 16:38

answered Feb 16 '18 at 16:29

Chris Lear

6,592
1
18
26

There doesn't seem to be a function that says "give me all the specific paths that match this wildcard-path". So I think the answer to your updated question is no. – Chris Lear Feb 16 '18 at 17:17

score 1 · Answer 2 · edited Sep 04 '19 at 06:23

1

It may be late but try this:

SET @junkJs:=JSON_OBJECT('a', JSON_OBJECT('b', 'xxxx'));

SET @x:=JSON_SEARCH(replace(@junkJs, '"b":', '"j":"junk", "b":'), 'all', 'junk');

select replace(@x, '.j', '');

edited Sep 04 '19 at 06:23

RKRK

1,284
5
14
18

answered Sep 04 '19 at 06:14

Edibius Meateus

21
1

Find in json by key in mysql

2 Answers2

Linked