PostgreSQL中json数据类型详解

前言

        JSON（JavaScript Object Notation）是一种轻量级的数据交换格式。它基于 ECMAScript（European Computer Manufacturers Association, 欧洲计算机协会制定的js规范）的一个子集，采用完全独立于编程语言的文本格式来存储和表示数据。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。 易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。其实JSON作为一种数据规范和标准，在用于接口交换，系统配置，数据存储方面拥有得天独厚的一席之地。

       在存储技术已经高速发达的今天，对于json数据的存储和使用，有多重方式。比如在缓存界的一哥Redis,文档数据库的佼佼者MongoDB等等。上述两者的基础数据结构也是JSON，基于json丰富的开发接口和支持。

      随着技术和业务的发展，除了上述两者之外，许多传统的关系型数据库，如mysql、PostgreSQL（以下简称PG）等等都开始支持json数据的存储和高效查询。在不增加额外的技术栈学习成本之下，基于统一的数据库库知识来满足日常的业务需求，也是一种合理的技术选型（满足了日常业务即可）。本文重点讲述在PG中关于json类型的介绍，json和jsonb的区别，json和jsonb的基本操作、输出区别、包含测试等内容，让各位对json类型有一个基本直观的认识。理解最基础的数据库操作。

一、PG数据库中JSON的类型

       json数据也可以被存储为text，但是 与text数据类型相比，JSON 数据类型的优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函 数和操作符可以用于存储在这些数据类型中的数据。基于text类型的数据，无法直接利用数据库的查询技术来提高查询效率。而且需要在应用程序中进行相关的转换。众所周知，PostgreSQL 提供存储JSON数据的两种类型：json 和 jsonb。

1、json和jsonb的区别

       json 和 jsonb数据类型接受几乎完全相同的值集合作为输入。 主要的实际区别之一是效率。json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也支 持索引，这也是一个令人瞩目的优势（言外之意，json类型对索引程度不是特别友好）。

       由于json类型存储的是输入文本的准确拷贝，其中可能会保留在语法 上不明显的、存在于记号之间的空格，还有 JSON 对象内部的键的顺序。还有， 如果一个值中的 JSON 对象包含同一个键超过一次，所有的键/值对都会被保留（ 处理函数会把最后的值当作有效值）。相反，jsonb不保留空格、不 保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键，只有 最后一个值会被保留。

2、项目开发中的选择

        从数据插入更新处理速度上，json>jsonb。在数据查询性能上jsonb>json。在数据的整体空间占用上，json>jsonb。

        因此，通常在一般的技术开发过程中，除非有特别特殊的需要（历史遗留问题等），大多数应用应该 更愿意把 JSON 数据存储为jsonb（通过json函数和函数索引的加持下，jsonb的查询能力得到了大大的增强）。

3、json数据类型

        在pg中的json数据类型可以分为：String，Number,boolean,Null。下面给出一个表格，是关于json的基本数据类型和pg数据类型的一个对比和对照。

        这里关于编码有一个需要解释的地方，就是Unicode的转义问题。这里涉及到数据库在创建的时候是不是使用utf-8的编码存储。在json类型的输入函数中，不管数据库 编码如何都允许 Unicode 转义，并且只检查语法正确性（即，跟在\u 后面的四个十六进制位）。但是，jsonb的输入函数更加严格：它不允 许非 ASCII 字符的 Unicode 转义（高于U+007F的那些），除非数据 库编码是 UTF8。jsonb类型也拒绝\u0000（因为 PostgreSQL的text类型无法表示 它），并且它坚持使用 Unicode 代理对来标记位于 Unicode 基本多语言平面之外 的字符是正确的。合法的 Unicode 转义会被转换成等价的 ASCII 或 UTF8 字符进 行存储，这包括把代理对折叠成一个单一字符。在把文本 JSON 输入转换成jsonb时，RFC 7159描述 的基本类型会被有效地映射到原生的 PostgreSQL类型（如 上表描述）。因此，在合法 jsonb数据的组成上有一些次要额外约束，它们不适合 json类型和抽象意义上的 JSON，这些约束对应于有关哪些东西不 能被底层数据类型表示的限制。尤其是，jsonb将拒绝位于 PostgreSQL numeric数据类型范 围之外的数字，而json则不会。不过，实际上这类问题更可能发生在其他实 现中，因为把 JSON 的number基本类型表示为 IEEE 754 双精度浮点 是很常见的（这也是RFC 7159 明确期待和允许的）。当在这类系 统间使用 JSON 作为一种交换格式时，应该考虑丢失数字精度的风险。

二、PG中json的简单操作

1、基础json数据操作

-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;
 
-- 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT '[1, 2, "foo", null]'::json;
 
-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"name": "张三", "age": 39, "active": false,"sex":"男"}'::json;
 
-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

2、json和jsonb输出对比

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)
 
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

        通过这里输出可以看到，将目标对象作为json输出时，输出结果和输入基本保持一致。 对于第二条语句而言，内容上似乎没有什么太大的变化，但是输出结果的顺序与第一条有明显的区别。

再来看一组有意思的输出，依然是关于jsonb和json的number结果的展示。

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

        很明显的区别是jsonb被数据库的执行引擎给优化了，展示结果与json也不同。

3、jsonb包含测试

        在很多的场景中，我们会使用API对两个json进行是否包含的判断，因为在json类型中，使用包含判断也是比较耗费时间的，在pg数据库中，天然提供了数据库层的包含函数，以此来提高查询匹配能力。在jsonb的查询中，使用@>进行包含的查询操作。

-- 右边具有一个单一键值对的对象被包含在左边的对象中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;
 
-- 右边的数组不会被认为包含在左边的数组中，
-- 即使其中嵌入了一个相似的数组：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- 得到假
 
-- 但是如果同样也有嵌套，包含就成立：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
 
-- 类似的，这个例子也不会被认为是包含：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- 得到假
 
-- 包含一个顶层键和一个空对象：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

总结

以上就是本文的基本内容，本文首先介绍了通用的json相关知识，然后重点讲述在PG中关于json类型的介绍，json和jsonb的区别，最后以案例的形式详细说明json和jsonb的基本操作、输出区别、包含测试等内容，让各位对json类型有一个基本直观的认识，理解最基础的数据库操作。

到此这篇关于PostgreSQL中json数据类型的文章就介绍到这了,更多相关PostgreSQL json类型内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！