What is the datatype to store json object into postgresql?

Question

I am very new to postgresql.

I want to store below json object into postgresql database.

{
  "host": "xxx.xxx.xx.xx"
  "type": "OS"
}

can you please advise me what data type should I use in postgresql. Thanks in advance.

Answer 1

PostgreSQL has two json data types. From Postgres docs:

There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

So TL;DR, Postgres's json stores the JSON as text and needs to re parse it on retrieval, whereas jsonb takes a little longer to store, but is already parsed on retrieval, and it can be used as an index in the db! So jsonb is probably the way to go most of the time

Answer 2

If your data always contains this same simple structure I don't see any reasons to store them as JSON. You should think about storing it simply in a table with columns host and type.

INSERT INTO table(my_host_column, my_type_column) VALUES
(my_json ->> 'host', my_json ->> 'type');

This makes many things much simpler (search, update, ...). In your case Postgres offers the inet type for IP adress columns. Such a column could do the plausibility checks for your host, for example (https://www.postgresql.org/docs/current/static/datatype-net-types.html)

You are able to recreate the JSON at any time with json_build_object('host', my_host_column, 'type', my_type_column) (https://www.postgresql.org/docs/current/static/functions-json.html)

---

But if you still want to store the JSON as it is:

If you do not want to do anything with it, store it as a text type (what I definitely do not recommend since you don't know what the future brings). If you want to use the JSON functions of Postgres you should store it as json or jsonb type (https://www.postgresql.org/docs/current/static/datatype-json.html).

jsonb has mostly an overhead of save space (more meta data) but is often significantly faster on operations.

Further reading:

Explanation of JSONB introduced by PostgreSQL

Faster Operations with the JSONB Data Type in PostgreSQL