[
关注
6月13日
-
6分钟阅读
你知道关系型数据库也能处理JSON数据吗？
JSON（JavaScript Object Notation）从它的JavaScript根基开始，已经走过了很长的路，现在已经成为简单而深入的结构化数据的事实机器对机器的数据交换标准。JSON数据的叶子节点只是数字、文本、布尔或空值形式的标量，而树状结构是由键值映射（又称 "对象"）和数组组成的。
JSON是一种基于文本的格式，需要进行解析才能发挥作用，而且它缺乏遵守模式的内置机制。大多数编程语言，包括网络浏览器中的JavaScript，都可以解析JSON，而且它被广泛用于REST和GraphQL APIs。
另一方面，JSON文档是一个扁平的、二维的树，没有与其他文档的链接。如果不强制执行结构，让访问数据库的编程（或人）来管理，大量的数据很快就会成为一种负担。
相比之下，非常严格的行和列的关系型数据库表可以通过引用成为高度维度的数据。它实际上是一棵所有叶子的树，因为列中的数据一般是完全标量的。关系型数据库的刚性允许智能索引、存储、确保数据和引用结构完整性的约束、唯一性保证，以及防止数据丢失。
PostgreSQL中的JSON支持
JSON支持在9.2版本中被添加到PostgreSQL中，此后被大大地扩展。
在PostgreSQL中，JSON数据可以以两种方式存储。JSON和JSONB，以便为这些数据类型开发有效的查询技术。
数据类型JSON和JSONB采取几乎相同的值集作为输入。最重要的实际区别是效率的区别。JSON数据类型存储了输入文本的精确拷贝，处理函数必须在每次执行时重新解析；然而， JSONB数据以分解的二进制格式存储，由于额外的转换开销，输入速度稍慢，但由于不需要重新解析，处理速度明显加快。JSONB也支持索引，这可能是一个很大的优点。
JSONB类型将保持符号之间的语义上重要的空白，以及JSON对象内的键的顺序，因为它存储了输入文本的精确拷贝。另外，如果同一个键在一个JSON对象中的值中出现了几次，所有的键/值对都会被保留。(处理程序将最近的值作为活动的值。) 另一方面，JSONB不保存重复的对象键，不保留空白，也不保留对象键的序列。如果在输入中提供了重复的键，只有最后的值被保存。在这篇文章中，我们将详细讨论JSON类型。
让我们从一个有JSON类型列的表开始，用字符串形式的JSON插入一些数据。
- **创建表：**emp_json包含两个字段，
id，它是一个主键，details，它是JSON类型的。
create table emp_json( id serial primary key, details json not null);
2.2. 向表中插入数据。
在将数据插入JSON列之前，你必须确认数据是有效的JSON格式。下面的INSERT语句在表emp_json中创建新的记录。
insert into emp_json(details) values('{"name":"BLAKE", "job":"analyst", "pay_details":{"sal":50000, "comm":500}}');
3.JSON数据的获取。
你使用SELECT语句来查询JSON数据，这与搜索其他本地数据类型是相同的。
select * from emp_json;
PostgreSQL产生一个JSON格式的结果集。
PostgreSQL有两个本地运算符用于查询JSON数据：-> 和->> 。
操作符-> 返回一个JSON对象字段的键。
操作符->> ，返回JSON对象字段的文本。
要从emp_json中获得JSON格式的所有名字，在下面的查询中使用操作符->。
select details->'name' as Name from emp_json;
而下面的查询使用操作符->> ，获得所有文本形式的名字。
select details->>'name' as Name from emp_json;
因为->操作符返回一个JSON对象，所以可以和->>操作符一起使用来获得一个特定的节点。例如，下面的语句会返回id和他们的工资。
select id, details->’pay_details’->>’sal’ as Salary from emp_json;
4.如何在WHERE子句中使用JSON操作符。
为了过滤返回的行，我们可以在WHERE子句中利用JSON操作符。例如，我们可以使用下面的查询来检索所有具有工作分析师且数值大于5000的id：
值得注意的是，在与5000比较之前，我们使用类型转换将qty字段改为INTEGER类型，因为->> 返回文本字段。
select id, details->'job' as job, details->'pay_details'->>'sal' as salary from emp_json where details->>'job' = 'analyst' orcast(details->'pay_details'->>'sal' as integer) >5000;
5.JSON数据的聚合函数。
select max(cast(details->'pay_details'->>'sal' as integer)), min(cast(details->'pay_details'->>'sal' as integer))from emp_json;
6.Postgres内建的JSON函数。
6.1 json_typeof()。
json typeof() 该方法返回一个字符串，代表最外层JSON值的类型。数字、布尔值、空值、对象、数组和字符串都是可能的。
下面的查询返回详细信息的数据类型。
select json_typeof(details ) from emp_json;
下面的查询返回'sal''字段的数据类型。
select json_typeof(details->'pay_details'->'sal' ) from emp_json;
6.2 json_object_keys()
json_object_keys()函数从最外层的JSON对象中返回一组键。下面的查询检索了详细信息对象的所有键。
select id, json_object_keys(details) from emp_json where id = 6;
6.3 json_each()
我们可以使用json_each()函数将最外层的JSON对象扩展为一组键值对。考虑一下下面的语句。
select id, json_each(details) from emp_json where id = 6;
如果你想深入挖掘，还有更多的PostgreSQL JSON函数。
我们已经学会了如何使用PostgreSQL的JSON数据类型，以及如何使用一些最重要的JSON运算符在本教程中更有效地处理JSON数据。
我希望你喜欢并从阅读这篇文章中学到东西。谢谢你花时间来阅读它。如果你喜欢这篇文章，请给它一个大拇指，并订阅我的Medium feed。
请留意其他文章。
