【MySQL】MySQL 8 的 JSON 新特性详解（2）JSON 函数

小雨青年 2023-03-28 原文

一、概述

在上一篇文章，我们学习了MySQL 8 的 JSON 相关的新特性，并搭建本地环境，尝试了MySQL 8 新支持的 JSON 数据类型。

下面，让我们继续学习MySQL 8 的 JSON 相关的新特性——JSON 函数。

JSON 函数：MySQL 8 提供了一组内置函数，允许您提取和操纵 JSON 数据。例如，JSON_EXTRACT 函数允许您从 JSON 文档中提取值，JSON_ARRAY 函数允许您创建 JSON 数组，JSON_OBJECT 函数允许您创建 JSON 对象。

二、本地环境和数据

由于之前做过详细说明，所以这次我只列出执行的命令。

1.创建docker环境

docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=password -p 3310:3306 -d mysql:8

2.创建初始数据

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

USE mydatabase;

CREATE TABLE mytable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NOT NULL,
  data JSON,
  UNIQUE KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_General_ci;

INSERT INTO mytable (name, email, phone, data) VALUES 
("John Doe", "johndoe@example.com", "555-555-5555", '{"address": {"city": "San Francisco", "state": "CA"}}'), 
("Jane Doe", "janedoe@example.com", "555-555-5556", '{"address": {"city": "New York", "state": "NY"}}'), 
("Jim Smith", "jimsmith@example.com", "555-555-5557", '{"address": {"city": "Los Angeles", "state": "CA"}}');

三、JSON 函数详解

1. JSON_EXTRACT()

JSON_EXTRACT()是MySQL8支持的一个函数，用于从JSON数据中提取指定的键或索引的值。该函数的语法如下：

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

参数说明：

json_doc：需要提取数据的JSON字符串。
path：提取数据的键或索引路径。

举个例子，如果我们有一个名为mytable的表，表中有一个名为data的JSON字段，那么我们可以使用以下语句来提取JSON字段中的地址信息：

SELECT name, email, phone, JSON_EXTRACT(data, '$.address') as address, 
       JSON_EXTRACT(data, '$.address.city') as city, JSON_EXTRACT(data, '$.address.state') as state
FROM mytable;

执行结果如下图所示。

这样，我们就可以通过JSON_EXTRACT()函数来提取JSON字段中的数据，并将其显示为普通字段。

2. JSON_SET() 、JSON_REPLACE()、 JSON_REMOVE()

这三个函数的用法差不多，所以放在一起，是对JSON文档进行插入替换移除。

JSON_SET(json_doc, path, val[, path, val] ...)

JSON_REPLACE(json_doc, path, val[, path, val] ...)

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

参数：

json_doc: 要更新的 JSON 文档。
path: 在文档中的路径，指向要替换的值的位置。
val: 要替换的值。

UPDATE mytable SET data = JSON_SET(data, '$.address.city', '22 Wall St.') WHERE id = 1;

UPDATE mytable SET data = JSON_REPLACE(data, '$.address.city', '23 Wall St.') WHERE id = 1;

UPDATE mytable SET data = JSON_REMOVE(data, '$.address.city') WHERE id = 1;

3.JSON_OBJECT()

JSON_OBJECT() 函数用于创建一个包含多个键值对的 JSON 对象。该函数接受任意数量的键值对参数，并将其作为 JSON 对象的键值对返回。

JSON_OBJECT(key1, value1, [key2, value2, ...])

比如。

SELECT JSON_OBJECT('name', 'John Doe', 'email', 'johndoe@example.com', 'age', 25);

结果如下。

{"name": "John Doe", "email": "johndoe@example.com", "age": 25}

他的在实际业务中的用法，是可以直接在SQL中返回json。

SELECT JSON_OBJECT(
  'id', id,
  'first_name', first_name,
  'last_name', last_name,
  'email', email,
  'address', address,
  'city', city,
  'state', state,
  'zip', zip
)
FROM users;

4.JSON_ARRAY()、 JSON_MERGE()

JSON_ARRAY() 函数可以将一组值转换为 JSON 数组，例如：

SELECT JSON_ARRAY(name, email) FROM mytable WHERE id = 1;

它的返回结果类似于：

["John Doe", "johndoe@example.com"]

在实际业务中，可以使用它来创建一组数据的 JSON 数组，例如：将多条数据的名字和邮件地址转换为一个 JSON 数组。

JSON_MERGE() 函数可以将多个 JSON 值合并为一个 JSON 值，例如：

SELECT JSON_MERGE(data, '{"age": 30}') FROM mytable WHERE id = 1;

它的返回结果类似于：

{"name": "John Doe", "email": "johndoe@example.com", "age": 30}

在实际业务中，可以使用它来合并多个 JSON 值，例如：将一个表的多条数据合并为一条 JSON 值，以方便更方便地处理数据。

5. JSON_QUOTE()

JSON_QUOTE() 函数用于在字符串的两端加上引号，并将其转换为一个 JSON 字符串。

举个例子，如果有一个名为 name 的字符串变量：

SET @name = 'John Doe';

您可以使用 JSON_QUOTE() 函数将其转换为一个 JSON 字符串：

SELECT JSON_QUOTE(@name) AS json_name;

返回值为

{"json_name":"John Doe"}

可以看到，JSON_QUOTE() 在字符串的两端添加了引号，并将其转换为了一个 JSON 字符串。

6.JSON_TYPE() 、JSON_VALID()

JSON_TYPE() 函数：该函数用于检测 JSON 表达式的类型，返回一个字符串表示 JSON 类型。支持的类型有：

OBJECT：表示 JSON 对象
ARRAY：表示 JSON 数组
STRING：表示字符串
INTEGER：表示整数
DECIMAL：表示小数
BOOLEAN：表示布尔值

SELECT 
  JSON_TYPE('{"name":"John", "age": 30}') as object_type,
  JSON_TYPE('["apple", "banana", "cherry"]') as array_type,
  JSON_TYPE('"Hello World"') as string_type,
  JSON_TYPE('42') as integer_type,
  JSON_TYPE('3.14') as decimal_type,
  JSON_TYPE('true') as boolean_type;

JSON_VALID() 函数：该函数用于检测 JSON 字符串是否合法，如果合法返回 true，否则返回 false。

SELECT 
  JSON_VALID('{"name":"John", "age": 30}') as valid_json,
  JSON_VALID('{"name":"John", age: 30}') as invalid_json;

7.JSON_SEARCH()

JSON_SEARCH() 函数用于在 JSON 数据中搜索某个字符串，并返回其位置。该函数有以下语法：

JSON_SEARCH(json_doc, one_or_all, search_str [, escape_char [, path]])

json_doc：要搜索的 JSON 数据
one_or_all：搜索模式，可以为“one”或“all”，表示只搜索一次或搜索所有结果
search_str：要搜索的字符串
escape_char：可选，指定一个字符来转义搜索字符串中的特殊字符
path：可选，指定搜索的路径

SELECT JSON_SEARCH(data, 'one', 'Baker');

这条语句将在 data 列中搜索字符串 'Baker'，并返回其位置。如果找到，则该函数返回 JSON 字符串的路径；如果没有找到，则返回 NULL。

8.JSON_UNQUOTE()

JSON_UNQUOTE() 函数可以解除引号并返回字符串。该函数接受一个参数，并返回不带引号的字符串。

SELECT JSON_UNQUOTE(data->>'$.address.city') AS city FROM mytable;

注意：如果数据不是字符串，则该函数不会解除引号。

四、总结

这次我们详细说明的MySQL 8 中 JSON 函数的用法。

有关【MySQL】MySQL 8 的 JSON 新特性详解（2）JSON 函数的更多相关文章

ruby-on-rails - Rails HTML 请求渲染 JSON - 2
在我的Controller中，我通过以下方式在我的index方法中支持HTML和JSON:respond_todo|format|format.htmlformat.json{renderjson:@user}end在浏览器中拉起它时，它会自然地以HTML呈现。但是，当我对/user资源进行内容类型为application/json的curl调用时(因为它是索引方法)，我仍然将HTML作为响应。如何获取JSON作为响应？我还需要说明什么？最佳答案您应该将.json附加到请求的url，提供的格式在routes.rb的路径中定义。这
ruby - 在没有 sass 引擎的情况下使用 sass 颜色函数 - 2
我想在一个没有Sass引擎的类中使用Sass颜色函数。我已经在项目中使用了sassgem，所以我认为搭载会像以下一样简单:classRectangleincludeSass::Script::FunctionsdefcolorSass::Script::Color.new([0x82,0x39,0x06])enddefrender#hamlengineexecutedwithcontextofself#sothatwithintemlateicouldcall#%stop{offset:'0%',stop:{color:lighten(color)}}endend更新:参见上面的#re
ruby-on-rails - 在 ruby 中使用 gsub 函数替换单词 - 2
我正在尝试用ruby中的gsub函数替换字符串中的某些单词，但有时效果很好，在某些情况下会出现此错误？这种格式有什么问题吗NoMethodError(undefinedmethod`gsub!'fornil:NilClass):模型.rbclassTest"replacethisID1",WAY=>"replacethisID2andID3",DELTA=>"replacethisID4"}end另一个模型.rbclassCheck 最佳答案啊，我找到了!gsub!是一个非常奇怪的方法。首先，它替换了字符串，所以它实际上修改了
ruby - 在 Ruby 中有条件地定义函数 - 2
我有一些代码在几个不同的位置之一运行:作为具有调试输出的命令行工具，作为不接受任何输出的更大程序的一部分，以及在Rails环境中。有时我需要根据代码的位置对代码进行细微的更改，我意识到以下样式似乎可行:print"Testingnestedfunctionsdefined\n"CLI=trueifCLIdeftest_printprint"CommandLineVersion\n"endelsedeftest_printprint"ReleaseVersion\n"endendtest_print()这导致:TestingnestedfunctionsdefinedCommandLin
ruby-on-rails - 如何使用 Rack 接收 JSON 对象 - 2
我有一个非常简单的RubyRack服务器，例如:app=Proc.newdo|env|req=Rack::Request.new(env).paramspreq.inspect[200,{'Content-Type'=>'text/plain'},['Somebody']]endRack::Handler::Thin.run(app,:Port=>4001,:threaded=>true)每当我使用JSON对象向服务器发送POSTHTTP请求时:{"session":{"accountId":String,"callId":String,"from":Object,"headers":
ruby - 在 Ruby 中按名称传递函数 - 2
如何在Ruby中按名称传递函数？(我使用Ruby才几个小时，所以我还在想办法。)nums=[1,2,3,4]#Thisworks,butismoreverbosethanI'dlikenums.eachdo|i|putsiend#InJS,Icouldjustdosomethinglike:#nums.forEach(console.log)#InF#,itwouldbesomethinglike:#List.iternums(printf"%A")#InRuby,IwishIcoulddosomethinglike:nums.eachputs在Ruby中能不能做到类似的简洁？我可以只
使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
C51单片机——实现用独立按键控制LED亮灭（调用函数篇） - 2
说在前面这部分我本来是合为一篇来写的，因为目的是一样的，都是通过独立按键来控制LED闪灭本质上是起到开关的作用，即调用函数和中断函数。但是写一篇太累了，我还是决定分为两篇写，这篇是调用函数篇。在本篇中你主要看到这些东西！！！1.调用函数的方法（主要讲语法和格式）2.独立按键如何控制LED亮灭3.程序中的一些细节（软件消抖等）1.调用函数的方法思路还是比较清晰地，就是通过按下按键来控制LED闪灭，即每按下一次，LED取反一次。重要的是，把按键与LED联系在一起。我打算用K1来作为开关，看了一下开发板原理图，K1连接的是单片机的P31口，当按下K1时，P31是与GND相连的，也就是说，当我按下去时
ruby - 用 YAML.load 解析 json 安全吗？ - 2
我正在使用ruby2.1.0我有一个json文件。例如:test.json{"item":[{"apple":1},{"banana":2}]}用YAML.load加载这个文件安全吗？YAML.load(File.read('test.json'))我正在尝试加载一个json或yaml格式的文件。最佳答案 YAML可以加载JSONYAML.load('{"something":"test","other":4}')=>{"something"=>"test","other"=>4}JSON将无法加载YAML。JSON.load("
ruby-on-rails - 将字符串转换为 ruby-on-rails 中的函数 - 2
我需要一个通过输入字符串进行计算的方法，像这样function="(a/b)*100"a=25b=50function.something>>50有什么方法吗？最佳答案您可以使用instance_eval:function="(a/b)*100"a=25.0b=50instance_evalfunction#=>50.0请注意，使用eval本质上是不安全的，尤其是当您使用外部输入时，因为它可能包含注入(inject)的恶意代码。另请注意，a设置为25.0而不是25，因为如果它是整数a/b将导致0(整数)。