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一定会是您的首选函数之一。