如何用JSON创建postgres表？

Latest release of PostgreSQL have capabilities to work like document oriented databases (e.g. MongoDB). There Is promising benchmarks that says postgres x times faster then mongo. Can someone give me advice how to work with postgres as with MongoDB. I'm seeking for step by step simple example concerned on

最新版本的PostgreSQL具有像面向文档的数据库（例如MongoDB）一样的功能。有充满希望的基准测试表明postgres比mongo快x倍。有人可以像MongoDB一样给我建议如何使用postgres。我正在寻找一步一步的简单例子

1) How to create simpliest table that contain JSON/JSONB objects like documents in mongodb

1）如何创建包含JONG / JSONB对象的最简单的表，如mongodb中的文档

2) How to make search on it at least by id, like I can do in mongodb with collection.find({id: 'objectId'}) for example

2）如何至少通过id进行搜索，就像我可以在mongodb中使用collection.find（{id：'objectId'}）进行搜索

3) How to create new object or overwrite existing at least by id, like I can do in mongodb with

3）如何创建新对象或至少通过id覆盖现有，就像我在mongodb中可以做的那样

 collection.update(
     {id: objectId},
     {$set: someSetObject, $unset: someUnsetObject}
     {upsert: true, w: 1}
  )

4) How to delete object if it exists at leas by id, like I can do in mongodb with collection.remove({id: 'objectId'})

4）如果id存在于leas中，如何删除对象，就像我可以在mongodb中使用collection.remove（{id：'objectId'}）

1 个解决方案

#1

It's too large topic to be covered in one answer. So there is just some examples as requested. For more information see documentation:

在一个答案中涵盖的主题太大了。所以只需要一些例子。有关更多信息，请参阅文档

8.14. JSON Types
8.14。 JSON类型
9.15. JSON Functions and Operators
9.15。 JSON函数和操作符

Create table:

创建表格：

create table test(
    id serial primary key,
    data jsonb);

Search by id:

按ID搜索：

select * from test where id = 1;

Search by json value:

按json值搜索：

select * from test where data->>'a' = '1';

Insert and update data:

插入和更新数据：

insert into test(id, data) values (1, '{"a": 1, "b": 2, "c": 3}');
update test set data = data - 'a' || '{"c": 5}' where id = 1;

Delete data by id:

按ID删除数据：

delete from test where id = 1;

Delete data by json value:

按json值删除数据：

delete from test where data->>'b' = '2';

#1