CODEWITHSUNDEEP
postgresqldatabase-design
Dilip Kr Singh
Dilip Kr Singh

Reputation: 1550

When we should use JSONB in PostgreSQL

I am new in PostgreSQL and want to know about JSONB datatype, when its use is useful and which scenario we should use JSONB datatype.

Upvotes: 0

Views: 86

Answers (1)

J Spratt
J Spratt

Reputation: 2012

It's useful for storing optional attributes on a row. For example, if you have a table cars where you normally store things like make, model, year...etc. (fields that apply to every car). You can store optional information such as color, wheel size, owner for a specific car record.

More concrete example:

CREATE TABLE node
( id       SERIAL      PRIMARY KEY
, parentid INTEGER
, created  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
, updated  TIMESTAMPTZ
, label    TEXT
, type     TEXT
, quantity INTEGER
, attr     JSONB DEFAULT '{}'
);

Then you might have a record like the following:

id|parentid|created            |updated            |label    |type  |quantity|attr
--|--------|-------------------|-------------------|---------|------|--------|---------------------------------------------|
2 |      1 |2019-08-01 00:00:00|2019-08-01 00:00:01|'part123'|'ITEM'|     10 |{'quantity-confirmed': '2019-08-01 00:00:01'}|

You can use this in a place where the table's normal form would otherwise be broken. Personally, I would create a separate table attributes where you have an objectid acting like a foreign key to some other record in your DB and store the attributes there.

CREATE TABLE attributes
( objectid INTEGER     PRIMARY KEY
, attr     JSONB
, created  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
, updated  TIMESTAMPTZ
);
CREATE INDEX attribute_idx_objectid ON attribute USING btree (objectid);
CREATE INDEX attribute_idx_attr     ON attribute USING gin   (attr);

Upvotes: 1

Related Questions