What is the datatype to store json object into postgresql?
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
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
Just store them as text type if no interaction is required (watch the maximum size for a text data type). Otherwise Postgresql supports JSON. Therefore, just read the corresponding documentation https://www.postgresql.org/docs/9.6/static/datatype-json.html
The advantage of the JSON types are, that Postgresql than analyses the content and you can use that later on for SELECT statements taking the JSON data structure into account.