You may encounter (or have already encountered) a situation you are dealing with JSON in your application and you want to store it in your database. Or have unpredictable data or have one or multiple columns in the table that are optional.

In these situations an object-oriented, NoSQL database makes sense.

But you're probably going to have to learn a new data query syntax, data creation statement, install new software, and run some new servers in production. Now rather than developing your features, you are going to be spending valuable time learning, experimenting.

Well my friend you can rest at ease since we will talk a little about how you can use PostgreSQL for all your JSON needs.

But let start first with what is JSON?

What is JSON?

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language Standard ECMA-262 3rd Edition - December 1999. JSON is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. These properties make JSON an ideal data-interchange language.

JSON is built on two structures:

• A collection of name/value pairs. In various languages, this is realized as an object, record, dictionary, hash table, keyed list, or associative array.

• An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence.

For more about JSON visit json.org.

JSON in PostgreSQL (JSON vs JSONB)

JSON

Since 9.2, released in September 2012, PostgreSQL has had a JSON type. This original JSON type was not much more than just a simple storage field that let you put JSON into your database table. It is just a simple text field that checks to make sure your JSON is well-formed. Other than that it doesn't do much and I would not recommend using it.

With PostgreSQL release 9.4 in December 2014, a JSOB type was added. Though the running joke is that the B stands for better it really stands for Binary. When you put JSON data into a JSONB column, in addition to checking for well-formed JSON, you now have the ability to indexing and query, and retrieve portions of the document. Generally for all your work you should use JSONB unless you have a compelling reason not to.

If you’re storing some form of log data you rarely need to query, JSON can work fine. Because it’s so simple, it will have a lot higher write throughput. For anything more complex, I’d recommend using JSONB, which is covered below.

JSONB (The one we will use here)

Finally, in Postgres 9.4 we got real and proper JSON in the form of JSONB. Though the running joke is that the B stands for better it really stands for Binary. JSONB is a binary representation of JSON, which means it’s compressed and more efficient for storage than just text. It also has similar plumbing of hstore underneath. In fact, once upon a time, there was almost hstore2 and a separate JSON type, but the two converged into the JSONB we have today.

JSONB is largely what you’d expect from a JSON datatype. It allows nested structures, use of basic data types, and has a number of built-in functions for working with it. Though the best part similar to hstore is the indexing. Creating a GIN index on a JSONB column will create an index on every key and value within that JSON document. That with the ability to nest within the document means JSONB is superior to hstore in most cases

Read more about When to use unstructured datatypes in Postgres–Hstore vs. JSON vs. JSONB

Creating a JSONB column

Let's say we're building a user_profile table with (id, first_name, last_name, user_name_id, email, phone_number, password) columns and an extra column with a JSONB type that will hold things like preferences, email_verified, ...

1CREATE TABLE user_profile (
2  id SERIAL NOT NULL PRIMARY KEY,
3  first_name TEXT NOT NULL,
4  last_name TEXT NOT NULL,
5  user_name_id TEXT NOT NULL UNIQUE,
6  email TEXT NOT NULL UNIQUE,
7  phone_number TEXT NOT NULL,
8  password TEXT NOT NULL,
9  extra **JSONB** DEFAULT '{}'::jsonb
10);
11
12INSERT INTO user_profile (id, first_name, last_name, user_name_id, email, phone_number, password, extra)
13VALUES (1, 'John', 'Doe', 'john-doe', 'test@gmail.com', '12345678', '011265048347', '{"email_verified": false, "preferences" : {"theme": "light","font_size": "2rem"}}');
14
15INSERT INTO user_profile (id, first_name, last_name, user_name_id, email, phone_number, password, extra)
16VALUES (2, 'Jane', 'Doe', 'jane-doe', 'test2@gmail.com', '87654321', '011265048347', '{"email_verified": true, "preferences" : {"theme": "light","font_size": "2rem"}}');
17
18SELECT * FROM user_profile;

1 id | first_name | last_name | user_name_id |      email      | phone_number |   password   |                                       extra
2----+------------+-----------+--------------+-----------------+--------------+--------------+-----------------------------------------------------------------------------------
3  1 | John       | Doe       | john-doe     | test@gmail.com  | 12345678     | 011265048347 | {"preferences": {"theme": "light"}, "email_verified": false}
4  2 | Jane       | Doe       | jane-doe     | test2@gmail.com | 87654321     | 011265048347 | {"preferences": {"theme": "light"}, "email_verified": true}
5(2 rows)

1SELECT extra FROM user_profile;

1                                       extra
2-----------------------------------------------------------------------------------
3 {"preferences": {"theme": "light"}, "email_verified": false}
4 {"preferences": {"theme": "light"}, "email_verified": true}
5(2 rows)

Querying data in JSON

[ "->" ] vs [ "->>" ] operator

PostgreSQL provides two native operators [ "->" ] and [ "->>" ] to help you query JSON data.

The operator [ "->" ] returns JSON object field as JSON so it can be chained.

1SELECT extra -> 'preferences' AS preferences FROM user_profile;

1               preferences
2-----------------------------------------
3 {"theme": "light"}
4 {"theme": "light"}
5(2 rows)

1SELECT extra -> 'preferences' -> 'theme' AS theme FROM user_profile;

1  theme
2---------
3 "light"
4 "light"
5(2 rows)

The operator [ "->>" ] returns the JSON object field as text so it can't be chained.

1SELECT extra ->> 'preferences' AS preferences FROM user_profile;

1               preferences
2-----------------------------------------
3 {"theme": "light"}
4 {"theme": "light"}
5(2 rows)

This will return an error

1postgres=# SELECT extra ->> 'preferences' ->> 'theme' AS theme FROM user_profile;

1ERROR:  operator does not exist: text ->> unknown
2LINE 1: SELECT extra ->> 'preferences' ->> 'theme' AS theme FROM use...
3                                       ^
4HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

And we can use one or multiple [ "->" ] with one [ "->>" ] at the end

1SELECT extra -> 'preferences' ->> 'theme' AS theme FROM user_profile;

1 theme
2-------
3 light
4 light
5(2 rows)

Insert data in JSON

Insert into JSON using the [ "||" ] operator

• The [ "||" ] operator Concatenate two JSONB values into a new JSONB value.

• So you can concatenate the column with the new values in a JSON format.

Insert into JSON Surface, level one path using the [ "||" ] operator

1UPDATE user_profile
2SET extra = extra || '{"phone_number_verified": true}'
3WHERE id = 1
4RETURNING extra;

1                                                      extra
2----------------------------------------------------------------------------------------------------------------
3{"preferences": {"theme": "light"}, "email_verified": false, "phone_number_verified": true}
4(1 row)
5
6
7UPDATE 1

Insert into JSON using the [ "jsonb_insert" ] function

Insert into JSON in one or multiple levels using the [ "jsonb_insert" ] function

The format for jsonb_insert:

1jsonb_insert(
2  target jsonb,
3  path text[],
4  new_value jsonb
5  [, insert_after boolean]
6)

Returns target with new_value inserted. If the target section designated by path is in a JSONB array, a new_value will be inserted before target or after if insert_after is true (default is false). If the target section designated by path is in the JSONB object, a new_value will be inserted only if the target does not exist. As with the path-oriented operators, negative integers appear in the path counter from the end of JSON arrays.

1UPDATE user_profile
2SET extra = jsonb_insert(
3"extra",
4'{preferences, hide}',
5'{"address": true, "phone_number": true, "email": true}'::jsonb
6)
7WHERE id = 1
8RETURNING extra;

1                                                                                     extra
2--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 {"preferences": {"hide": {"email": true, "address": true, "phone_number": true}, "theme": "dark"}, "email_verified": false, "phone_number_verified": true}
4(1 row)

Update data in JSON

Updating JSON using the [ "||" ] operator

• The [ "||" ] operator Concatenate two JSONB values into a new JSONB value.

• So you can concatenate the column with the new values in a JSON format.

• Since the key already exists and we are using JSONB not JSON type it will override the old value

Updating JSON Surface, level one for one or multiple values using the [ "||" ] operator

1UPDATE user_profile
2SET extra = extra || '{"email_verified": true}'
3WHERE id = 1
4RETURNING extra;

1                                                      extra
2-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3{"preferences": {"hide": {"email": true, "address": true, "phone_number": true}, "theme": "light"}, "email_verified": true, "phone_number_verified": true}
4(1 row)
5
6
7UPDATE 1

Updating JSON using the [ "jsonb_set" ] function

The format for jsonb_set:

1jsonb_set(
2  target jsonb,
3  path text[],
4  new_value jsonb,
5  [create_missing boolean try by default]
6)

Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and the item designated by path does not exist. As with the path-oriented operators, negative integers appear in the path counter from the end of JSON arrays.

Updating JSON Surface, level one path for one value using the [ "jsonb_set" ] function

• if we want to target email_verified the path would be [ "'{email_verified}'" ].

1  UPDATE user_profile
2  SET extra = jsonb_set(
3  "extra",
4  '{email_verified}',
5  'false'
6  )
7  WHERE id = 1
8  RETURNING extra;

1                                                      extra
2--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3{"preferences": {"hide": {"email": true, "address": true, "phone_number": true}, "theme": "light"}, "email_verified": false, "phone_number_verified": true}
4(1 row)

Updating JSON deep, multiple levels for one value using the [ "jsonb_set" ] function

• if we want to target a theme in preferences the path would be [ "'{preferences,theme}'" ].

1UPDATE user_profile
2SET extra = jsonb_set(
3"extra",
4'{preferences, theme}',
5'"dark"'
6)
7WHERE id = 1
8RETURNING extra;

1                                                    extra
2-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3{"preferences": {"hide": {"email": true, "address": true, "phone_number": true}, "theme": "dark"}, "email_verified": false, "phone_number_verified": true}
4(1 row)

Updating multiple values in JSON using the [ "jsonb_set" ] function

1UPDATE user_profile
2SET extra = jsonb_set(
3  jsonb_set(
4    "extra",
5    '{preferences, hide, first_name}',
6    'true'
7  ),
8  '{preferences, hide, last_name}',
9  'true'
10)
11WHERE id = 1
12RETURNING extra;

1                                                                                                         extra
2-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 {"preferences": {"hide": {"email": true, "address": true, "last_name": true, "first_name": true, "phone_number": true}, "theme": "dark"}, "email_verified": true, "phone_number_verified": true}
4(1 row)

Ok, what's happening here?

• The inner [ "jsonb_set" ] changing the [ "first_name" ] value to [ "true" ] and returns a new data.

• The outer [ "jsonb_set" ] take the new data change [ "last_name" ] to [ "true" ] and returns new data.

• Then you update the [ "extra" ] column with the new modified data.

Delete data in JSON

Delete data in JSON using the [ "#-" ] operator

1UPDATE user_profile
2SET extra = extra #- '{preferences, hide, email}'
3WHERE id = 1
4RETURNING extra;

1                                                                                                extra
2-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 {"preferences": {"hide": {"address": true, "last_name": true, "first_name": true, "phone_number": true}, "theme": "dark", "font_size": "2rem"}, "email_verified": true, "phone_number_verified": true}
4(1 row)

Since the [ "#-" ] operator returns a JSON object we can chain it.

1UPDATE user_profile
2SET extra = extra #- '{preferences, hide, first_name}' #- '{preferences, hide, last_name}'
3WHERE id = 1
4RETURNING extra;

1                                                                              extra
2--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 {"preferences": {"hide": {"address": true, "phone_number": true}, "theme": "dark", "font_size": "2rem"}, "email_verified": true, "phone_number_verified": true}
4(1 row)

Resources

• 9.16. JSON Functions and Operators (postgresql.org)

• When to use unstructured datatypes in Postgres–Hstore vs. JSON vs. JSONB (citusdata.com)

• Using PostgreSQL for JSON Storage (blog.crunchydata.com)

• How to: use jsonb_set function in PostgreSQL (dev.to)

• Postgresql - update or delete a value from a nested jsonb element (stackoverflow.com)

• postgres jsonb_set multiple keys update (stackoverflow.com)

• How to update multiple nested keys in a json field in single update query? (stackoverflow.com)

• Postgres jsonb_set multiple nested fields (stackoverflow.com)

• postgresql: jsonb update multiple keys in one query (stackoverflow.com)

• What is the difference between [ "->>" ] and [ "->" ] in Postgres SQL? (stackoverflow.com)