合 PG数据类型之json和jsonb
简介
PG中所有数据类型参考：https://www.dbaup.com/postgresql-shujuleixingjianjie.html
官网说明参考：http://postgres.cn/docs/13/datatype-json.html
postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。
注意：键值对的键必须使用双引号
JSON 基本类型和相应的PostgreSQL类型
| JSON 基本类型 | PostgreSQL类型 | 注释 |
|---|---|---|
string | text | \u0000是不允许的，因为 Unicode 转义表示数据库编码中不可用的字符 |
number | numeric | 不允许NaN 和 infinity值 |
boolean | boolean | 只接受小写true和false拼写 |
null | (无) | SQL NULL是一个不同的概念 |
从PostgreSQL 9.3开始，json就成了postgres里的一种数据类型，也就是和varchar、int一样，我们表里的一个字段的类型可以为json了。
与此同时，postgres还提供了jsonb格式，jsonb格式是json的二进制形式，二者的区别在于json写入快，读取慢，jsonb写入慢，读取快，但在操作上，二者是没有区别的。
JSON 输入和输出语法
下列都是合法的json（或者jsonb）表达式：
1 2 3 4 5 6 7 8 9 10 11 12 13 | -- 简单标量/基本值 -- 基本值可以是数字、带引号的字符串、true、false或者null SELECT '5'::json; -- 有零个或者更多元素的数组（元素不需要为同一类型） SELECT '[1, 2, "foo", null]'::json; -- 包含键值对的对象 -- 注意对象键必须总是带引号的字符串 SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json; -- 数组和对象可以被任意嵌套 SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json; |
如前所述，当一个 JSON 值被输入并且接着不做任何附加处理就输出时， json会输出和输入完全相同的文本，而jsonb 则不会保留语义上没有意义的细节（例如空格）。例如，注意下面的不同：
1 2 3 4 5 6 7 8 9 10 11 | 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中数据会被按照底层 numeric类型的行为来打印。实际上，这意味着用E记号 输入的数字被打印出来时就不会有该记号，例如：
1 2 3 4 5 | SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb; json | jsonb -----------------------+------------------------- {"reading": 1.230e-5} | {"reading": 0.00001230} (1 row) |
不过，如这个例子所示，jsonb将会保留拖尾的小数点后的零，即便这 对于等值检查等目的来说是语义上无意义的。
创建表
假设我们要存储的json数据是这样的：
1 2 3 4 5 | { "id": ID "name":"名字", "age":年龄 } |
建表语句如下：
1 2 3 4 5 6 7 8 9 10 | create table if not exists name_age ( info jsonb ); postgres=# \d name_age Table "public.name_age" Column | Type | Collation | Nullable | Default --------+-------+-----------+----------+--------- info | jsonb | | | |
好了，这样就创建了一张表，里面只有一个 info 字段，下面开始进行CRUD操作。
插入数据
插入数据可以直接以json格式插入：
1 | insert into name_age values('{"id":1,"name":"小明", "age":18}'); |
在json里插入新的key值gender，如下：
