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的允许的。然而，实际上这个问题是更可能在其他的实现中发生的，因为按照IEEE 754双精浮点去表示json的值的原生类型是相同的。当使用json和系统作为交换格式时，postgresql应该考虑接受丢失数据精确性的危险还是选择存储原始数据。

相反，注释中对输入json原生类型格式的一些辅助限制不会添加给对应的postgresql数据类型

1.1.1 JSON Input and Output syntax

在RFC 7159中指定了json数据类型的输入输出的语法。

下面都是有效的json表达式：

-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::json;

-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::json;

-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Arrays and objects can be nested arbitrarily 任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

按照之前的状态，当一个json值输入然后没有任何额外操作的情况打印，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)

在jsonb中，有一个无语义的细节需要注意，数字会按照它的根本数字类型的形式被打印。实际上这意味着用E符号表示的数字不会显示E(科学计数法)。举例如下：

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

但是，jsonb会保留末尾的0，正如上面的例子，目的是用作质量检查

1.1.2 Designing JSON documents effectively

在流动的环境中使用json表示数据比传统的关系型数据模型更灵活。在同一个应用中两种类型共存并补充彼此是可能的。尽管在应用中期待最大限度的柔性，但是仍旧建议json文档有一个固定式的结构。结构是典型的非强制性的（即使强制使用一些商业规则的声明式可行的），但是一个预期的结构可以让我们更容易的去写概括表中文档这样的请求语句。 json数据同其他保存于数据表中的数据一样受制于并发控制代价。尽管保存一个大文档可行，但是要记住任何的更新都会获得一个行级锁。为了在更新事务中中降低锁的争夺可以考虑限制json文档的可控范围。理想情况下，JSON文档应该代表一个原子数据，业务规则所规定的数据不能被合理地进一步细分为可以独立修改的较小的数据。

1.1.3 jsonb Containment and Existence

测试包含是jsonb的一项重要的能力。json类型则没有对应的一套设施。包含检测一个jsonb文档是否包含在另一个jsonb中。下面的例子都返回true，只是要注意： ``` -- Simple scalar/primitive values contain only the identical value: SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

– The array on the right side is contained within the one on the left:
SELECT ‘[1, 2, 3]’::jsonb @> ‘[1, 3]’::jsonb;

– Order of array elements is not significant, so this is also true:
SELECT ‘[1, 2, 3]’::jsonb @> ‘[3, 1]’::jsonb;

– Duplicate array elements don’t matter either:
SELECT ‘[1, 2, 3]’::jsonb @> ‘[1, 2, 2]’::jsonb;

– The object with a single pair on the right side is contained
– within the object on the left side:
SELECT ‘{“product”: “PostgreSQL”, “version”: 9.4, “jsonb”:true}’::jsonb @> ‘{“version”:9.4}’::jsonb;

– The array on the right side is not considered contained within the
– array on the left, even though a similar array is nested within it:
SELECT ‘[1, 2, [1, 3]]’::jsonb @> ‘[1, 3]’::jsonb; – yields false

– But with a layer of nesting, it is contained:
SELECT ‘[1, 2, [1, 3]]’::jsonb @> ‘[[1, 3]]’::jsonb;

– Similarly, containment is not reported here:
SELECT ‘{“foo”: {“bar”: “baz”}}’::jsonb @> ‘{“bar”: “baz”}’::jsonb; – yields false

通常的规则是在从包含对象中舍弃一些非匹配的数组元素或者键值对对象之后被包含的对象必须作为结构和数据内容匹配包含对象。但是要记住当做包含匹配时数据元素的顺序是没有意义的，并且重复的键只会匹配一次。
一个特殊的例外是，一个数组只有键，那么结构必须被匹配

– This array contains the primitive string value:
SELECT ‘[“foo”, “bar”]’::jsonb @> ‘“bar”’::jsonb;

– This exception is not reciprocal – non-containment is reported here:
SELECT ‘“bar”’::jsonb @> ‘[“bar”]’::jsonb; – yields false

jsonb拥有existence操作符。在容器主题中是一个变量：它检测是否一个字符串出现在一个对象的key或者值为json对象的顶级目录中。

– String exists as array element:
SELECT ‘[“foo”, “bar”, “baz”]’::jsonb ? ‘bar’;

– String exists as object key:
SELECT ‘{“foo”: “bar”}’::jsonb ? ‘foo’;

– Object values are not considered:
SELECT ‘{“foo”: “bar”}’::jsonb ? ‘bar’; – yields false

– As with containment, existence must match at the top level:
SELECT ‘{“foo”: {“bar”: “baz”}}’::jsonb ? ‘bar’; – yields false

– A string is considered to exist if it matches a primitive JSON string:
SELECT ‘“foo”’::jsonb ? ‘foo’;

当涉及到许多键或元素时，JSON对象比数组更适合用于测试容器或存在，因为与数组不同，它们在内部优化搜索，不需要线性搜索。

_Tips:因为json对象是嵌套式的，一个合适的查询可以跳过直接去选择子对象。一个例子，我们有一个doc列，包含一个数组类型的子对象，请求会找到查询包含子对象的入口，而忽视数组外的其他键_

SELECT doc->‘site_name’ FROM websites
WHERE doc @> ‘{“tags”:[{“term”:“paris”}, {“term”:“food”}]}’;

_下面的操作也能达到同样的效果_

SELECT doc->‘site_name’ FROM websites
WHERE doc->‘tags’ @> ‘[{“term”:“paris”}, {“term”:“food”}]’;

_但是这样的方法缺少弹性，并且效率低。另一个是jsonb的existence是不支持嵌套的，它只搜索顶级目录的键或者数组元素_

---

<h5>1.1.4 jsonb Indexing</h5>

支持两种操作，默认和jsonb_path_ops

jsonb_path_ops虽然只支持@>操作，但是需要的空间小，易于文档的扩展

建立gin索引

操作 @>, ?, ?& and ?| 会使用索引

create index doc_idx_gin on public.user using gin (info);

只有 @> 操作使用索引

create index doc_idx_gin on public.user using gin (info jsonb_path_ops);

可以在二级key建立索引 @>, ?, ?& and ?|

create index doc_a_idx_gin on public.user using gin ((doc -> ‘a’));

也支持btree类型的索引

_更多关于索引的操作，也许下次分享_

---

<h3>2. json数据</h3>

##### 2.1、json数据：

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

- 开发中，我们可以选择正常数据类型中存储必须的数据，使用无模式的数据类型（json数据类型）来存储额外的数据，以这样的方式来作为我们解决数据存储的一种解决方案  

##### 2.2、json和jsonb：

postgresql对json的支持主要有两种类型，分别是json和jsonb，输入时它们接收的值集几乎是相同的，细微的区别如下：

- _json:_

    1. 存储输入文本的精确副本
    2. 冲解析输入函数的每一条执行

- _jsonb:_

    1. 存储输入文本的二进制格式
    2. 增加转换的开销，提升执行的速度
    3. 支持索引

- json写入快，读取慢，jsonb写入慢，读取快。除非特殊的需求，比如需要保留键的顺序，而大多数情况我们选择使用jsonb

<h3 id="2-json-example">3、json_example</h3>

##### 3.1、定义json数据类型

from tyrion.core.database import db
from sqlalchemy.dialects.postgresql import JSON, JSONB

class User(db.Model):
id = db.Column(db.Integer(), primary_key=True)
username = db.Column(db.String(255))
password = db.Column(db.String(255))

info = db.Column(JSONB)

$ CREATE TABLE “user” (
id SERIAL NOT NULL,
username VARCHAR(255),
password VARCHAR(255),
info JSONB,
PRIMARY KEY (id)
)

##### 3.2、写入json数据

postgres=# insert into public.user (username, password, info) values (
‘fanbin’,
‘110’,
‘{
“addr”: “vintag”,
“is_display”: true,
“msg”: [
{“msg_id”: 0, “msg_info”: “test_json”},
{“msg_id”: 1, “msg_info”: “test_json”}
]
}’
)

##### 2.3、json数据的操作
[官方文档](https://www.postgresql.org/docs/9.3/static/functions-json.html)

select ‘[1,2,3]’::json->2; --return 3
select ‘{“a”:1,“b”:2}’::json->‘b’; --return 2
select ‘[1,2,3]’::json->>2; --return ‘3’
select ‘{“a”:1,“b”:2}’::json->>‘b’; --return ‘2’
select ‘{“a”:[1,2],“b”:[3,4]}’::json#>’{a,0}’; --return 1
select ‘{“a”:[1,2],“b”:[3,4]}’::json#>>’{a,0}’; --return ‘1’

select ‘{“a”:1}’::jsonb <@ ‘{“a”:1,“b”:2}’::jsonb;
select ‘{“a”:1,“b”:2,“c”:3}’::jsonb ?| array[‘b’,‘c’];
select ‘{“a”:1,“b”:2,“c”:3}’::jsonb ?| array[‘f’,‘e’];
select ‘{“a”:1,“b”:2,“c”:3}’::jsonb ?& array[‘a’,‘b’];
select ‘{“a”:1,“b”:2,“c”:3}’::jsonb ?& array[‘a’,‘f’];

select (’[1,2,3]’::json->2)::int;
ERROR: cannot cast type json to integer
LINE 1: select (’[1,2,3]’::json->2)::int;
^
select (’[1,2,3]’::json->>2)::int; --return 3

info
{
“addr”: “vintag”,
“is_display”: true,
“msg”: [
{“msg_id”: 0, “msg_info”: “test_json”},
{“msg_id”: 1, “msg_info”: “test_json”}
]
}

select * from “user” where info->>“addr” = “vintag”;
select * from “user” where (info->>“is_display”)::boolean is true;
select * from “user” where (info#>’{msg, 0}’->>‘msg_id’)::int = 0;

##### 2.4、json函数

返回json数组最外层元素的个数

select json_array_length(’[0,1,{“f1”:1,“f2”:[2,3]},[2,3]]’); --return 4

每个键值组成的一条记录，key是text，value是json

select * from json_each(’{“a”:“foo”, “b”:“bar”}’)
key | value
-----±------
a | “foo”
b | “bar”

返回的key和value都是text

select * from json_each_text(’{“a”:“foo”, “b”:“bar”}’);
key | value
-----±------
a | foo
b | bar

select json_extract_path(’{“f2”:{“f3”:1},“f4”:{“f5”:99,“f6”:“foo”}}’,‘f4’)
–return {“f5”:99,“f6”:“foo”}

select json_extract_path_text(’{“f2”:{“f3”:1},“f4”:{“f5”:99,“f6”:“foo”}}’,‘f4’, ‘f6’)
–return foo

返回key的集合：text

select json_object_keys(’{“f1”:“abc”,“f2”:{“f3”:“a”, “f4”:“b”}}’)
json_object_keys

f1
f2

返回json对象value的集合

select json_array_elements(’[1,true, [2,false]]’);
value

1
true
[2,false]

---

下面是什么鬼！！！

---

sqlalchemy-json常用操作

json_user = User.query.filter(User.info[(‘beijing’)] == ‘beijing’)

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

json_user = User.query.filter(User.info[(‘beijing’)].cast(db.String) == ‘beijing’)

可以查询，但是并不能匹配到结果

json_user = User.query.filter(User.info[(‘addr’)].astext.cast(db.String) == ‘beijing’)

json_user.count() --> 3

json_user = User.query.filter(User.info[(‘no’)].astext.cast(db.Integer) == 99)

json_user.count() --> 1

json_user = User.query.filter(User.info[(‘no’)].astext.cast(db.String) == ‘99’)

json_user.count() --> 1

select * from public.user where (info#>’{person, 0}’->>‘gender’)::int = ‘female’;

json_user.count() --> 1

sqlalchemy syntax

json_user = User.query.filter(User.info[(‘person’, 0, ‘gender’)].astext.cast(db.String) == ‘female’)

Mutation Tracking



提供支持对标量值进行就地更改的跟踪，将其传播到拥有父对象的ORM更改事件中。

from sqlalchemy.types import TypeDecorator, VARCHAR
import json

class JSONEncodedDict(TypeDecorator):
“Represents an immutable structure as a json-encoded string.”

impl = VARCHAR

def process_bind_param(self, value, dialect):
    if value is not None:
        value = json.dumps(value)
    return value

def process_result_value(self, value, dialect):
    if value is not None:
        value = json.loads(value)
    return value

1. 关系型数据库与非关系型数据库区别问题

2. json/jsonb存储、查询速度问题

3. value的格式问题

4. 两张表之间的关联relationship问题