How to create postgres table with JSON?

Question

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

1) How to create simpliest table that contain JSON/JSONB objects like documents in 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

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

 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'})

Ildar Musin · Accepted Answer · 2016-02-25 19:15:16Z

3

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

Create table:

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

Search by id:

select * from test where id = 1;

Search by json value:

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:

delete from test where id = 1;

Delete data by json value:

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

edited Feb 25, 2016 at 19:15

answered Feb 25, 2016 at 11:32

Ildar Musin

1,4881 gold badge12 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ihor Romanchenko Over a year ago

You may have a mistake in your answer: JSON operator -> returns JSON. To return a JSON element for comparison you need to use ->>. Example data->>'a' = '1'

Ildar Musin Over a year ago

You probably right. -> returns jsonb and ->> returns text. But it's not clear to me which one better to use for comparison. Anyway both variants works.

Ildar Musin Over a year ago

It turns out that it can be a little tricky to search by text value with -> operator: where data->>'a' = 'test' vs. where data->'a' = '"test"' (we have to use double quotes to make it convertible to jsnob). So I updated answer.

Collectives™ on Stack Overflow

How to create postgres table with JSON?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related