MySQL 8.0中的一个新的JSON函数是JSON_TABLE。它也是MySQL的第一个表函数。也就是说，返回值不是标量值而是结果集。JSON_TABLE将JSON文档(部分)转换为关系表。在这篇博客文章中，我将向您展示如何做到这一点，并讨论JSON_TABLE如何启用使用SQL处理JSON数据的新方法。
JSON_TABLE例子
首先，我将创建一个表t1，其中的一列为JSON类型，并向表中插入一个JSON文档:
CREATE TABLE t1(json_col JSON);
INSERT INTO t1 VALUES (
'{ "people": [
{ "name":"John Smith", "address":"780 Mission St, San Francisco, CA 94103"},
{ "name":"Sally Brown", "address":"75 37th Ave S, St Cloud, MN 94103"},
{ "name":"John Johnson", "address":"1262 Roosevelt Trail, Raymond, ME 04071"}
我可以使用以下SQL查询转换为关系表的文件:
SELECT people.*
FROM t1,
JSON_TABLE(json_col, '$.people[*]' COLUMNS (
name VARCHAR(40) PATH '$.name',
address VARCHAR(100) PATH '$.address')
) people;
因为JSON_TABLE返回一个结果集，所以可以在FROM子句中使用它。JSON_TABLE接受以下参数:
1.JSON数据源:这个表达式可以引用FROM列表中前面表中的列。在本例中，json_col指的是包含JSON文档的列。(注意，按照SQL标准的规定，前面的表和引用该表的JSON_TABLE之间有一个隐式的横向连接(lateral join)。换句话说，对于前面的表的每一行，将“调用”JSON_TABLE函数。)
2.应该转换为表的JSON数组的路径。在本例中，它是people数组的对象。
3.那些表中的列，其列名、类型和路径应该在JSON对象中可以找到值。
我们还需要给返回的表一个名称，这里我们称它为people。
以上查询将返回以下结果:
ADDRESS
John Smith
780 Mission St, San Francisco, CA 94103
Sally Brown
75 37th Ave S, St Cloud, MN 9410
John Johnson
1262 Roosevelt Trail, Raymond, ME 04071
对JSON文档的关系操作
当我们使用JSON_TABLE将JSON文档转换为表时，我们可以使用“SQL工具集”并对数据执行诸如选择和聚合之类的关系操作。例如，我们可以使用这个查询来选择只叫John的人:
SELECT people.*
FROM t1,
JSON_TABLE(json_col, '$.people[*]' COLUMNS (
name VARCHAR(40) PATH '$.name',
address VARCHAR(100) PATH '$.address')
) people;
WHERE people.name LIKE 'John%';
ADDRESS
John Smith
780 Mission St, San Francisco, CA 94103
John Johnson
1262 Roosevelt Trail, Raymond, ME 04071
另一个MySQL 8.0中的新函数JSON_ARRAYAGG，我们现在可以将结果转换回JSON文档:
SELECT JSON_OBJECT("people",
JSON_ARRAYAGG(JSON_OBJECT("name", name, "address", address))) json_doc
FROM t1,
JSON_TABLE(json_col, '$.people[*]' COLUMNS (
name VARCHAR(40) PATH '$.name',
address VARCHAR(100) PATH '$.address')
) people;
WHERE people.name LIKE 'John%';
JSON文档
{"people": [{"name": "John Smith", "address": "780 Mission St, San Francisco, CA 94103"}, {"name": "John Johnson", "address": "1262 Roosevelt Trail, Raymond, ME 04071"}]}
如上所示，我们可以通过JSON_TABLE和JSON_ARRAYAGG对JSON文档执行关系操作。
嵌套的JSON数组
JSON_TABLE还可以处理嵌套JSON数组。给定以下JSON数组与家族对象，有数组与子对象:
"father": "John",
"mother": "Mary",
"children": [
"age": 12,
"name": "Eric"
"age": 10,
"name": "Beth"
"marriage_date": "2003-12-05"
"father": "Paul",
"mother": "Laura",
"children": [
"age": 9,
"name": "Sarah"
"age": 3,
"name": "Noah"
"age": 1,
"name": "Peter"
我们想要将这个文档转换为每个子元素对应一行的表:
FATHER
MARRIED
CHILD_ID
CHILD
Sarah
Peter
JSON_TABLE (families, '$[*]' COLUMNS (
id FOR ORDINALITY,
father VARCHAR(30) PATH '$.father',
married INTEGER EXISTS PATH '$.marriage_date',
NESTED PATH '$.children[*]' COLUMNS (
child_id FOR ORDINALITY,
child VARCHAR(30) PATH '$.name',
age INTEGER PATH '$.age') )
我们使用嵌套路径来指定应该从子数组中提取哪些值。这个示例还表明，我们可以通过指定序数而不是路径来为行分配id。我们还可以使用EXISTS PATH检查路径是否存在。这里，如果找到结婚日期，则married为1，否则为0。
对JSON数据进行SQL聚合
一旦我们使用JSON_TABLE将JSON数据转换为关系表，我们就可以利用SQL聚合来计算JSON数据的计数、总和、平均值等。使用上面提供的例子，这个查询将计算每个家庭孩子的平均年龄:
SELECT father, COUNT(*) "#children", AVG(age) "age average"
FROM t,
JSON_TABLE (families, '$[*]' COLUMNS (
id FOR ORDINALITY,
father VARCHAR(30) PATH '$.father',
NESTED PATH '$.children[*]' COLUMNS (
age INTEGER PATH '$.age' ) )
) fam
GROUP BY id, father;
ATHER#CHILDREN
AVERAGE
11.0000
4.3333
我们甚至可以使用JSON_MERGE_PATCH函数将计算出来的数据放回JSON文档中:
SELECT JSON_ARRAYAGG(fam_obj) families
FROM (
SELECT JSON_MERGE_PATCH(family,
JSON_OBJECT("#children", COUNT(*), "avg_age" , AVG(age))) fam_obj
FROM t,
JSON_TABLE (families, '$[*]' COLUMNS (
id FOR ORDINALITY,
family JSON PATH '$',
NESTED PATH '$.children[*]' COLUMNS (
age INTEGER PATH '$.age' ) )
) fam
GROUP BY id, family) fams;
这里，我们将现有的family对象与计算后的总和和平均值构造的对象合并。然后，JSON_ARRAYAGG将把所有合并的对象放回一个数组中。
在这篇博客文章中，我展示了如何使用JSON_TABLE对JSON数据执行关系操作。使用JSON_ARRAYAGG，您可以采取相反的做法，将结果集转换为JSON文档。这能让你两全其美;您可以将数据存储为JSON格式，但同时也可以利用SQL的强大功能。
感谢您使用MySQL !
原文链接：JSON_TABLE – The Best of Both Worlds September 16, 2018 Øystein Grøvlen
前端提升生产力系列二（vue3 element-plus 配置json快速生成table列表组件）
在PC端日常的使用中，使用最多的过于表单和列表了，故此对table列表和form表单进行了统一的封装，通过json配置就可以快速适配table列表和form表单。
python数据可视化开发(2)：pandas读取Excel的数据格式处理（数据读取、指定列数据、DataFrame转json、数学运算、透视表运算输出）
python数据可视化开发(2)：pandas读取Excel的数据格式处理（数据读取、指定列数据、DataFrame转json、数学运算、透视表运算输出）
php使用webSocket实现Echarts长连接自动刷新的解决方案(2)：后端服务端代码返回json数据
php使用webSocket实现Echarts长连接自动刷新的解决方案(2)：后端服务端代码返回json数据
php使用webSocket实现Echarts长连接自动刷新的解决方案(1)：前端获取后端JSON数据
php使用webSocket实现Echarts长连接自动刷新的解决方案(1)：前端获取后端JSON数据
2019-nCov疫情实时趋势数据可视化Echarts学习(3):JSON数据和echarts柱图和折线图表的开发
2019-nCov疫情实时趋势数据可视化Echarts学习(3):JSON数据和echarts柱图和折线图表的开发
漏刻有时API接口实战开发系列(8)：ajax获取本地json实现echarts不同数据图表渲染的解决方案
漏刻有时API接口实战开发系列(8)：ajax获取本地json实现echarts不同数据图表渲染的解决方案
漏刻有时API接口实战开发系列(7)：PHP将API获取的json数据自动同步到mysql数据库的解决方案
漏刻有时API接口实战开发系列(7)：PHP将API获取的json数据自动同步到mysql数据库的解决方案
可视化JSON数据工具推荐：JSON Viewer Pro和JSONGrid
本文介绍了两款可视化JSON数据的工具：JSON Viewer Pro和JSONGrid。它们都提供了丰富的功能和用户友好的界面，使用户能够更轻松地理解和处理JSON格式的数据。这些功能包括查看和分析、编辑和修改、格式化和美化、折叠和展开、高亮和搜索、排序和过滤、导入和导出等。这些工具对于开发人员、数据分析师和任何需要处理JSON的人都非常实用。
