JSON_EXTRACT函數詳解

JSON_EXTRACT函數是MySQL 5.7版本及以上的新特性之一，它使得我們能夠直接從JSON對象中提取數據。這篇文章將從以下幾個方面對JSON_EXTRACT函數做詳細的闡述：

一、JSON_EXTRACT函數的語法

JSON_EXTRACT函數的語法如下：

JSON_EXTRACT(json_doc, path[, path] ...)

其中，json_doc是要提取數據的JSON文檔，而path則是要提取的數據路徑，可以是一個或多個路徑參數。path參數還支持字符串函數，如CONCAT和SUBSTRING，這些函數可以與路徑參數一起使用。

二、JSON_EXTRACT函數的示例

讓我們通過幾個示例來進一步了解JSON_EXTRACT函數的使用。

1. 從JSON數組中提取元素：可以使用以下命令從JSON數組中提取第一個元素：

SELECT JSON_EXTRACT('[1, 2, 3]', '$[0]');  -- 返回值為1

2. 從嵌套的JSON對象中提取值：假設我們有以下JSON對象：

{
    "name": "John",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "Anytown",
        "state": "TX",
        "zip": "12345"
    }
}

我們可以使用以下命令從中提取州名稱：

SELECT JSON_EXTRACT('{
    "name": "John",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "Anytown",
        "state": "TX",
        "zip": "12345"
    }
}', '$.address.state');  -- 返回值為"TX"

3. 提取嵌套的JSON數組：假設我們有以下JSON對象：

{
    "store": {
        "book": [
            {
                "category": "reference",
                "author": "Nigel Rees",
                "title": "Sayings of the Century",
                "price": 8.95
            },
            {
                "category": "fiction",
                "author": "Evelyn Waugh",
                "title": "Sword of Honour",
                "price": 12.99
            },
            {
                "category": "fiction",
                "author": "Herman Melville",
                "title": "Moby Dick",
                "isbn": "0-553-21311-3",
                "price": 8.99
            },
            {
                "category": "fiction",
                "author": "J. R. R. Tolkien",
                "title": "The Lord of the Rings",
                "isbn": "0-395-19395-8",
                "price": 22.99
            }
        ]
    }
}

我們可以使用以下命令從中提取所有書籍的價格：

SELECT JSON_EXTRACT('{
    "store": {
        "book": [
            {
                "category": "reference",
                "author": "Nigel Rees",
                "title": "Sayings of the Century",
                "price": 8.95
            },
            {
                "category": "fiction",
                "author": "Evelyn Waugh",
                "title": "Sword of Honour",
                "price": 12.99
            },
            {
                "category": "fiction",
                "author": "Herman Melville",
                "title": "Moby Dick",
                "isbn": "0-553-21311-3",
                "price": 8.99
            },
            {
                "category": "fiction",
                "author": "J. R. R. Tolkien",
                "title": "The Lord of the Rings",
                "isbn": "0-395-19395-8",
                "price": 22.99
            }
        ]
    }
}', '$.store.book[*].price');

該命令將返回一個JSON數組，其中包含所有書籍的價格。

三、使用JSON_EXTRACT函數進行數據過濾

除了從JSON文檔中提取數據，JSON_EXTRACT函數還能用於進行數據過濾。通過在WHERE子句中使用JSON_EXTRACT函數，我們可以僅查詢符合特定條件的記錄。

假設我們有以下JSON文檔：

{
  "students": [
    {
      "name": "John Doe",
      "gender": "male",
      "age": 18,
      "graduated": false,
      "scores": [
        {
          "subject": "math",
          "score": 98
        },
        {
          "subject": "history",
          "score": 88
        }
      ]
    },
    {
      "name": "Jane Doe",
      "gender": "female",
      "age": 19,
      "graduated": true,
      "scores": [
        {
          "subject": "math",
          "score": 78
        },
        {
          "subject": "history",
          "score": 92
        }
      ]
    }
  ]
}

我們可以使用以下命令查詢出年齡大於18歲且數學分數大於90分的學生：

SELECT JSON_EXTRACT('{
  "students": [
    {
      "name": "John Doe",
      "gender": "male",
      "age": 18,
      "graduated": false,
      "scores": [
        {
          "subject": "math",
          "score": 98
        },
        {
          "subject": "history",
          "score": 88
        }
      ]
    },
    {
      "name": "Jane Doe",
      "gender": "female",
      "age": 19,
      "graduated": true,
      "scores": [
        {
          "subject": "math",
          "score": 78
        },
        {
          "subject": "history",
          "score": 92
        }
      ]
    }
  ]
}', '$.students[?(@.age > 18 && @.scores[?(@.subject == "math")].score > 90)]');

該命令將返回一個JSON數組，其中包含所有符合條件的學生信息。

四、使用CONCAT和SUBSTRING函數進行數據處理

我們可以在JSON_EXTRACT函數中使用字符串函數進行數據處理，以下是幾個示例：

1. 使用CONCAT函數連接字符串路徑：假設我們要從以下JSON對象中提取州名稱：

{
    "name": "John",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "Anytown",
        "state": "TX",
        "zip": "12345"
    }
}

我們可以使用以下命令從中提取州名稱：

SELECT JSON_EXTRACT('{
    "name": "John",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "Anytown",
        "state": "TX",
        "zip": "12345"
    }
}', CONCAT('$.address.', 'state'));  -- 返回值為"TX"

2. 使用SUBSTRING函數截取路徑：假設我們有以下JSON對象：

{
    "product_name": "Awesome Product",
    "features": [
        {
            "name": "Feature A",
            "description": "This is feature A."
        },
        {
            "name": "Feature B",
            "description": "This is feature B."
        },
        {
            "name": "Feature C",
            "description": "This is feature C."
        }
    ]
}

我們可以使用以下命令只獲取features子節點：

SELECT JSON_EXTRACT('{
    "product_name": "Awesome Product",
    "features": [
        {
            "name": "Feature A",
            "description": "This is feature A."
        },
        {
            "name": "Feature B",
            "description": "This is feature B."
        },
        {
            "name": "Feature C",
            "description": "This is feature C."
        }
    ]
}', SUBSTRING('$.features', 2));  -- 返回值為[{...}]

結論

以上就是JSON_EXTRACT函數的詳細介紹，它使得我們能夠方便地從JSON文檔中提取數據，並進行數據過濾和處理。如果您在使用MySQL 5.7及以上版本，那麼JSON_EXTRACT一定會是您的首選函數之一。