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"}

• user330315: create table documents (id integer primary key, content jsonb)
• Nag: @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.

Answer 1

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.

• Nag: 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: 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: Can you elaborate on why one is better for 'analysis' vs 'static storage'?
• Patrick: @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.