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 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
– Raymond Nijland, Commented Feb 16, 2018 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
– Chris Lear, Commented Feb 16, 2018 at 16:22
JSON_EXTRACT(@jsonstr,'$**.Acronym') is closer to what you're trying to get hold of — Chris Lear
– Chris Lear, Commented Feb 16, 2018 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
– Chris Lear, Commented Feb 16, 2018 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
– Raymond Nijland, Commented Feb 16, 2018 at 17:04

Chris Lear · Accepted Answer · 2018-02-16 16:38:38Z

8

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, 2018 at 16:38

answered Feb 16, 2018 at 16:29

Chris Lear

6,7601 gold badge20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Lear Over a year ago

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.

RKRK · Accepted Answer · 2019-09-04 06:23:38Z

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 4, 2019 at 6:23

RKRK

1,2945 gold badges14 silver badges18 bronze badges

answered Sep 4, 2019 at 6:14

Edibius Meateus

211 bronze badge

Collectives™ on Stack Overflow

Find in json by key in mysql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related