How to create table to store json object data in PostgreSQL database?

Question

I am new to PostgreSQL database(9.5.0), I need to store my json data in PostgreSQL database, so that I need to create a table for it, how can I create a table for it to store my json object on clicking of submit button from my front-end, so that it will be stored in created database table and my sample josn object is as follows(having key/value pairs, contains files also), Please help me.

{"key1":"value1","key2":"value2","key3":"value3","key4_file_name":"Test.txt"}

create table documents (id integer primary key, content jsonb) — user330315
– user330315, Commented Jan 18, 2016 at 9:07
@a_horse_with_no_name, thanks for your reply, so does it store all my json values in a table(for all my key/values and files if any) ? Because I am new to this. Thanks. — Nag
– Nag, Commented Jan 18, 2016 at 9:10

Patrick · Accepted Answer · 2016-01-18 09:07:34Z

10

PostgreSQL has the json and jsonb (b for binary) data types. You can use these in your table definitions just like any other data type. If you plan to merely store the json data then the json data type is best. If, on the other hand, you plan to do a lot of analysis with the json data inside the PG server, then the jsonb data type is best.

answered Jan 18, 2016 at 9:07

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

4 Comments

Nag Over a year ago

Hi Patrick, thanks for your reply, so is it fine ? create table test(content json); ? so that it will store all my above json object into this table(test).

Patrick Over a year ago

Yes, that is all that it takes. Every row in the database can then hold one json document. You probably want to add some other columns to identify the json document by id or user name, for instance, but in principle this is all that you need.

FreyGeospatial Over a year ago

Can you elaborate on why one is better for 'analysis' vs 'static storage'?

Patrick Over a year ago

@FreyGeospatial Processing json data in PG implies that the document in text format has to be parsed before any operations take place on it, which is very inefficient compared to jsonb which does not require such preprocessing. If the JSON document is only stored in PG and not processed, I would only use json data only if the client application requires the JSON document in text format.

Collectives™ on Stack Overflow

How to create table to store json object data in PostgreSQL database?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related