postgresql 删除字段如果存在_PostgreSQL何以支持丰富的NoSQL特性？

作者介绍

谭峰: 网名francs，中国开源软件推进联盟PostgreSQL分会特聘专家，《PostgreSQL实战》作者之一，《PostgreSQL 9 Administration Cookbook》译者之一，致力于PostgreSQL技术分享，博客https://postgres.fun

张文升:中国开源软件推进联盟PostgreSQL分会核心成员之一，《PostgreSQL实战》作者之一。常年活跃于PostgreSQL、MySQL、Redis等开源技术社区，坚持推动PostgreSQL在中国地区的发展，多次参与组织PostgreSQL全国用户大会。近年来致力于推动PostgreSQL在互联网企业的应用以及企业PostgreSQL培训与技术支持。

引言

上篇文章《PostgreSQL用户应掌握的高级SQL特性》介绍了PostgreSQL的典型高级SQL特性，PostgreSQL不仅是关系型数据库，同时支持丰富的NoSQL特性，本篇文章将从《PostgreSQL实战》第9章摘选部分内容介绍PostgreSQL的NoSQL特性，分以下三部分介绍。

• PostgreSQL的JSON和JSONB数据类型简介

• JSON与JSONB读写性能测试

• PostgreSQL全文检索支持JSON和JSONB(PosgreSQL 10 新特性)

一、PostgreSQL的JSON和JSONB数据类型

PostgreSQL支持非关系数据类型json(JavaScript Object Notation)，本节介绍json类型、json与jsonb差异、json与jsonb操作符和函数，以及jsonb键值的追加、删除、更新。

1.1 JSON类型简介

PotgreSQL早在9.2版本已经提供了json类型，并且随着大版本的演进，PostgreSQL对json的支持趋于完善，例如提供更多的json函数和操作符方便应用开发，一个简单的json类型例子如下：

mydb=> SELECT '{"a":1,"b":2}'::json;     json      --------------- {"a":1,"b":2}

为了更好演示json类型，接下来创建一张表，如下所示：

mydb=> CREATE TABLE test_json1 (id serial primary key,name json);CREATE TABLE

以上示例定义字段name为json类型，插入表数据，如下所示：

mydb=> INSERT INTO test_json1 (name) VALUES ('{"col1":1,"col2":"francs","col3":"male"}');INSERT 0 1mydb=> INSERT INTO test_json1 (name) VALUES ('{"col1":2,"col2":"fp","col3":"female"}');INSERT 0 1

查询表test_json1数据：

mydb=> SELECT * FROM test_json1; ??id |                   name                   ----+------------------------------------------  1 | {"col1":1,"col2":"francs","col3":"male"}  2 | {"col1":2,"col2":"fp","col3":"female"}

1.2 查询JSON数据

通过->操作符可以查询json数据的键值，如下所示：

mydb=> SELECT  name -> 'col2' FROM test_json1 WHERE id=1; ?column? ---------- "francs"(1 row)

如果想以文本格式返回json字段键值可以使用->>符，如下所示：

mydb=> SELECT  name ->> 'col2' FROM test_json1 WHERE id=1; ?column? ---------- francs(1 row)

1.3  JSONB与JSON差异

PostgreSQL支持两种JSON数据类型：json和jsonb，两种类型在使用上几乎完全相同，两者主要区别为以下：json存储格式为文本而jsonb存储格式为二进制 ，由于存储格式的不同使得两种json数据类型的处理效率不一样，json类型以文本存储并且存储的内容和输入数据一样，当检索json数据时必须重新解析，而jsonb以二进制形式存储已解析好的数据，当检索jsonb数据时不需要重新解析，因此json写入比jsonb快，但检索比jsonb慢，后面会通过测试验证两者读写性能差异。除了上述介绍的区别之外，json与jsonb在使用过程中还存在差异，例如jsonb输出的键的顺序和输入不一样，如下所示：

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

而json的输出键的顺序和输入完全一样，如下所示：

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

另外，jsonb类型会去掉输入数据中键值的空格，如下所示：

mydb=> SELECT ' {"id":1,    "name":"francs"}'::jsonb;            jsonb            ----------------------------- {"id": 1, "name": "francs"}(1 row)

上例中id键与name键输入时是有空格的，输出显示空格键被删除，而json的输出和输入一样，不会删掉空格键：

mydb=> SELECT ' {"id":1,    "name":"francs"}'::json;             json              -------------------------------  {"id":1,    "name":"francs"}(1 row)

另外，jsonb会删除重复的键，仅保留最后一个，如下所示：

mydb=> SELECT ' {"id":1,                                                              "name":"francs","remark":"a good guy!","name":"test"}'::jsonb;                       jsonb                        ---------------------------------------------------- {"id": 1, "name": "test", "remark": "a good guy!"}(1 row)

上面name键重复，仅保留最后一个name键的值，而json数据类型会保留重复的键值。相比json大多数应用场景建议使用jsonb，除非有特殊的需求，比如对json的键顺序有特殊的要求。

1.4 JSONB与JSON操作符

PostgreSQL支持丰富的JSONB和JSON的操作符，举例如下：以文本格式返回json类型的字段键值可以使用->>符，如下所示>：

mydb=> SELECT  name ->> 'col2' FROM test_json1 WHERE id=1; ?column? ---------- francs(1 row)

字符串是否作为顶层键值，如下所示：

mydb=> SELECT '{"a":1, "b":2}'::jsonb ? 'a'; ?column? ---------- t(1 row)

删除json数据的键/值，如下所示：

mydb=> SELECT '{"a":1, "b":2}'::jsonb - 'a'; ?column? ---------- {"b": 2}(1 row)

1.5 JSONB与JSON函数

json与jsonb相关的函数非常丰富，举例如下：扩展最外层的json对象成为一组键/值结果集，如下所示：

mydb=> SELECT * FROM json_each('{"a":"foo", "b":"bar"}'); key | value -----+------- a   | "foo" b   | "bar"(2 rows)

以文本形式返回结果，如下所示：

mydb=> SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}'); key | value -----+------- a   | foo b   | bar(2 rows)

一个非常重要的函数为row_to_json()函数，能够将行作为json对象返回，此函数常用来生成json测试数据，比如将一个普通表转换成json类型表：

mydb=> SELECT * FROM test_copy WHERE id=1; id | name ----+------  1 | a(1 row)mydb=> SELECT row_to_json(test_copy) FROM test_copy WHERE id=1;     row_to_json     --------------------- {"id":1,"name":"a"}(1 row)

返回最外层的json对像中的键的集合，如下所示：

mydb=> SELECT * FROM json_object_keys('{"a":"foo", "b":"bar"}'); json_object_keys ------------------ a b(2 rows)

1.6 jsonb键/值的追加、删除、更新

jsonb键/值追加可通过||操作符，如下增加sex键/值：

m