postgresql-json

1 overview

2 json_example

---

1、overview

三个问题：

1.什么是json数据类型？

2.为什么要使用json数据类型？

3.如何使用json数据类型？

1.1 JSON TYPE

官方文档

按照RFC 7159的说明，json数据类型是用来存储json数据的。json数据可以作为text存储，但json数据类型的优点是每个存储的值都是有效符合json规则的，而且还可以提供各种json特有的函数及操作符。

有两种json数据类型：json和jsonb。他们接收几乎完全一致的值集作为输入，主要的实际差别在与效率。json完全copy输入文本来进行存储，其中处理函数必须对每个执行进行重解析；而jsonb类型的数据被分解为二进制格式存储，因为需要转换使其在写入时稍慢，但是由于不需要重解析所以处理起来明显快的多。jsonb也支持索引，这是一个非常重要的优势。

scripts/cal_time_sql_json.py

因为json类型存储的是输入文本的精确副本，它将保存token之间的无语义的空白，以及json对象中key的顺序。而且，如果值内的一个json对象包含多个相同的key，它也会对其完整的存储。处理函数会取最后一个值作为操作符。相反，jsonb不会保存空白，不会保存对象key的顺序，也不会保留重复的key。如果输入时特别指明了重复的key，只保存最后一个值。

postgres=# select '{"a": 1, "b":2, "c":  3}'::json;
postgres=# select '{"a": 1, "b":2, "c":  3}'::jsonb;
postgres=# insert into public.user (info, doc) values ('{"a": 1, "b":2, "c":  3}', '{"a": 1, "b":2, "c":  3}');

通常，大多数应用趋向于将json数据保存为jsonb类型，除非有十分特别的需要，例如关于对象key的顺序的遗留假设。

PostgresSQL在每个数据库中只允许设定一个字符集。因此不可能确保json类型严格的遵守json特性，除非数据库的编码类型是utf8.如果尝试直接包含无法在数据库编码中表示的字符会；相反，在数据库编码中而不在utf8编码中表示的字符是被允许的。

RFC 7159允许json字符串包含由 \uXXXX表示的Unicode转移序列。在json类型的输入函数中，Unicode转义允许忽略数据库编码，并且仅检查语法的正确性(也就是说，4个十六进制数字跟在u后面)。然而，jsonb的输入函数却是严格的：它不允许Unicode转义字符为非ASCII码，除非数据库编码是utf8.jsonb类型也拒绝\u0000（因为它不能在postgresql的text类型中表示出来），它强调任何使用Unicode代理对（Unicode surrogate pairs）在Unicode基础多语言的基础上来指定字符是正确的。有效的Unicode转义字符会被转换为等效的ASCII码或者是utf8字符来存储；包括将代理对打包成一个字符 。

note：在Session 9.15描述的许多json处理函数将unicode转义字符转换为普通字符，因此，即使他们的输入是json类型而不是jsonb，也会抛出同样的类型错误。事实上，json输入函数不会将这些检查成为历史组件，尽管它允许将json Unicode转义字符简单的存储到一个非utf8编码的数据库中。通常，最好避免使unicode转义字符的json和非utf8数据库编码混合。

postgres=# select '{"a": 1, "b":2, "c":  "\u0061"}'::jsonb;

postgres=# select '{"a": 1, "b":2, "c":  "\u0000"}'::jsonb;
ERROR:  unsupported Unicode escape sequence
LINE 1: select '{"a": 1, "b":2, "c":  "\u0000"}'::jsonb;
               ^
DETAIL:  \u0000 cannot be converted to text.
CONTEXT:  JSON data, line 1: {"a": 1, "b":2, "c":...

当把文本的json转换输入jsonb时，在RFC 7159中描述的原始类型会映射成原生的postgresql数据类型。因此，会有一些次要的辅助限制来组成有效的jsonb数据，但不会添加给json类型，也不会添加给提取的json，相应的去限制那些可以被一下数据类型表示的数据。最明显的，jsonb会拒绝postgresql数据类型之外的值，而json不会。这个严格的定义实现是通过RFC 7159的允许的。然而，实际上这个问题是更可能在其他的实现中发生的࿰