在mysql中按键查找json [英] Find in json by key in mysql

问题描述

我有一个json，我想提取一个键的值.我知道密钥名称，但不知道位置.我的json很复杂，在mysql中看起来像这样:

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"
                }
            }
        }
    }
}
';

例如，我要搜索关键字"Acronym".我想要完整路径/或直接输入它的值["SGML"]，以便我可以进一步处理它.

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.

我正在执行以下操作，因此返回null

Im doing the below and its returning me null

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

我的要求:

• 我的json中可能有多个首字母缩写"，我只想全部使用.
• 我没有价值部分，我只知道钥匙
• 我的json带有嵌套的对象和数组，很复杂

---

编辑

它有效，所以我现在尝试使用更新后的json来查找

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"
                }
            }
        }
    }
}
';

现在查询

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

返回

["SGML"，另一个值"]

["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.

推荐答案

我认为您需要此查询:

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

JSON_Search在JSON对象中查找值.您知道要查找的密钥的名称，因此您只需要使用路径即可，该路径在 https://dev.mysql.com/doc/refman/5.7/zh-CN/json-search-functions.html#function_json-search

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

这篇关于在mysql中按键查找json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！