jsonb
, a new column type for storing documents in binary JSON format. This makes it slightly slower to input due to added conversion overhead and might take more disk space to store. On the other hand it is significantly faster to process, since no reparsing is needed and supports indexing, which can be of a serious advantage.
This article demonstrates usage of jsonb
operations and storage mechanics. To fully understand the design of jsonb
data type and what you can do with it, check the official documentation on JSON Types and JSON Functions and Operators.
Introduction
Before we get started, let's get familiar with the sample structure we'll use in this example series.
In these examples we use the familiar model with users
, orders
and products
tables. Let's create them.
CREATE TABLE products (
id serial PRIMARY KEY,
data jsonb
);
CREATE TABLE users (
id serial PRIMARY KEY,
data jsonb
);
CREATE TABLE orders (
id serial PRIMARY KEY,
data jsonb
);
Next, let's add sample data with which we will work with.
-- PRODUCTS: inserting some data
INSERT INTO products (DATA) VALUES (
'{
"Name": "Laptop",
"Price": "500.00"
}'
);
INSERT INTO products (DATA) VALUES (
'{
"Name": "Mechanical Keyboard",
"Price": "120.00"
}'
);
-- USERS: inserting some data
INSERT INTO users (DATA) VALUES (
'{
"FirstName": "John",
"LastName": "Doe",
"Address": {
"City": "Skopje",
"Country": "Macedonia"
}
}'
);
-- ORDERS: inserting some data
INSERT INTO orders (DATA) VALUES (
'{
"Number": "555",
"UserId": "1",
"Items": [
{
"ProductId": "1",
"Name": "TV",
"Price": "300.00"
},
{
"ProductId": "2",
"Name": "Mechanical Keyboard",
"Price": "120.00"
}
]
}'
);
Querying
To query jsonb
documents we are going to use special operators as described in JSON Functions and Operators.
The two main operators for getting results out of jsonb
data are:
->
returnsjsonb
object field or array by key->>
returnsjsonb
object field as text
This article demonstrates usage of jsonb
operations and storage mechanics. To fully understand the design of jsonb
data type and what you can do with it, check the official documentation on JSON Types.
Query for specific fields from jsonb
data
SELECT
u.id as id,
u.data->>'FirstName' as first_name,
u.data->>'LastName' as last_name,
u.data->'Address'->>'Country' as country
FROM users AS u
WHERE u.data->>'FirstName' = 'John';
With ->>
we get the exact field from the jsonb
document as text, used for returning FirstName
and LastName
. With ->
we get jsonb
object (e.g. Address
) and with ->>
in it we get actual field value (e.g. Country
).
JOIN two tables and GROUP results
The query we want to demonstrate is: Find all users that made an order, and for each user display how many orders they've made.
SELECT
u.id as user_id,
u.data->>'FirstName' as first_name,
u.data->>'LastName' as last_name,
COUNT(o.id) as orders_count
FROM orders AS o
INNER JOIN users as u ON u.id = cast(o.data->>'UserId' as numeric)
GROUP BY user_id, first_name, last_name;
As you can see, even when working with document-oriented approach, you can still utilize the relational features in SQL. Furthermore, if you want to deal with real foreign keys, to ensure relational integrity, you can work with duplicate columns and add foreign keys to them. Such implementation as example can be found in Marten tool.
Fulltext search
SELECT *
FROM users AS u
WHERE u.data->>'FirstName' ~* '.*hn.*';
This query will find an user which FirstName
contains hn
. This means that John
the user will be found with this query.
To understand how this works, please refer to Postgres documentation on Pattern Matching.
Adding
To add fields and values in jsonb
, deep object structures, or arrays, we must refer back to JSON Processing Functions article.
For many of the operations we use the jsonb_set
function which is defined like:
jsonb_set(target jsonb, path text[], new_value jsonb, [create_missing boolean])
It 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.
Add field
UPDATE users
SET data = jsonb_set(data, '{Age}', '"30"', TRUE)
WHERE id = 1 -- John Doe
This query adds field Age
with value 30
in data
document.
Result:
{
"FirstName": "John",
"LastName": "Doe",
"Address": {
"City": "Skopje",
"Country": "Macedonia"
},
"Age": "30"
}
Add array
UPDATE users
SET data = jsonb_set(data, '{Phones}', '["123456", "999888777", "222333555"]', TRUE)
WHERE id = 1;
This query adds whole array of values to table.
Result:
{
"FirstName": "John",
"LastName": "Doe",
"Address": {
"City": "Skopje",
"Country": "Macedonia"
}
"Phones": ["123456", "999888777", "222333555"]
}
Add value in array
UPDATE users
SET data = jsonb_set(data, '{Phones, 999999}', '"123"', TRUE)
WHERE id = 1;
This query appends new phone number in the array of phone numbers. 999999
is the position at which we want to place the order. We set this number high in order to add the new value in the last position of the array. The value you add in the array can be a simple numeric value or complex JSON object.
Result:
{
"FirstName": "John",
"LastName": "Doe",
"Address": {
"City": "Skopje",
"Country": "Macedonia"
}
"Phones": ["123456", "999888777", "222333555", "123"]
}
Add field in nested object
UPDATE users
SET data = jsonb_set(data, '{Address,Street}', '"Majakovski 2"', TRUE)
WHERE id = 1;
This query adds field Street
with value Majakovski 2
in Address
object, as part of users data column.
Result:
{
"FirstName": "John",
"LastName": "Doe",
"Address": {
"City": "Skopje",
"Country": "Macedonia",
"Street": "Majakovski 2"
}
}
Add field in nested object within array of objects
UPDATE orders
SET data = jsonb_set(
data,
'{Items}', -- the array in which we operate
to_jsonb(
(WITH ar AS(
WITH temp AS(
SELECT data->'Items' AS items -- the array in which we operate
FROM orders
WHERE id = 1 -- the filtered order we are updating
)
SELECT jsonb_set(
jsonb_array_elements(items),
'{Quantity}', -- the new field we are adding
'"1"', -- the value of the new field
true)
FROM temp)
SELECT (array_agg(ar.jsonb_set))
FROM ar)),
false)
WHERE id = 1;
This query adds Quantity
field with value 1
in each object in Items
array from orders
database table.
In order to cast the data we are getting out of the temporary result we use to_jsonb
function.
to_jsonb(anyelement)
Returns the value as jsonb
. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid jsonb value.
Result - as you can see, Quantity
field with value 1
is added in each object in Items
array:
{
"Number": "555",
"UserId": "1",
"Items": [
{
"ProductId": "1",
"Name": "TV",
"Price": "300.00",
"Quantity": "1"
},
{
"ProductId": "2",
"Name": "Mechanical Keyboard",
"Price": "120.00",
"Quantity": "1"
}
]
}
Updating
To update values of fields in jsonb
we must refer back to JSON Processing Functions article.
For many of the operations we use the jsonb_set
function which is defined like:
jsonb_set(target jsonb, path text[], new_value jsonb, [create_missing boolean])
It 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.
Update value of a field
UPDATE users
SET data = jsonb_set(data, '{FirstName}', '"Minesota"', FALSE)
WHERE id = 1 -- John Doe
This query updates the value of FirstName
to Minesota
for user John Doe
. In jsonb_set
function, the create_missing
parameter is set to FALSE
, which means only already existing field can be updated - it won't create new field if it doesn't exist (which setting it to TRUE
does).
Result:
{
"FirstName": "Minesota",
"LastName": "Doe",
}
Update element of an array
UPDATE users
SET data = jsonb_set(data, '{Phones,0}', '"333"', FALSE)
WHERE id = 1;
This query updates the first element of Phones
array to 333
.
Before:
{
"FirstName": "John",
"LastName": "Doe",
"Address": {
"City": "Skopje",
"Country": "Macedonia"
}
"Phones": ["123456", "999888777", "222333555"]
}
After:
{
"FirstName": "John",
"LastName": "Doe",
"Address": {
"City": "Skopje",
"Country": "Macedonia"
}
"Phones": ["333", "999888777", "222333555"]
}
Deleting
To delete elements from jsonb
we must refer to Additional JSONB Operators article.
Delete field
UPDATE users
SET data = data - 'Address'
WHERE id = 1
This query removes Address
nested object.
Delete field from nested object
UPDATE users
SET data = data #- '{Address,City}'
WHERE id = 1
This query removes City
field from Address
nested object.
Delete element from array
UPDATE orders
SET data = data #- '{Items,2}';
This query deletes the third element from Items
array.
Renaming field
UPDATE users
SET data = data - 'FirstName' || jsonb_build_object('Name', data->'FirstName')
This query renames FirstName
to Name
, by removing FirstName
and adding Name
with preset value from FirstName
. Yes, jsonb
operators are powerful combined with SQL.
Summary
The features jsonb
column type brings are so powerful that afford you to handle any document-oriented data structures very well and on the other side with a very significant gain in performance. All this brings us advantages when combining it with the practicality of a NoSQL document-oriented solution, but still using the powerfull features of a RDBMS.For more examples and JSON functions and operators refer to the official PostgreSQL documentation.
References
- Is PostgreSQL Your Next JSON Database?
- Using PostgreSQL and jsonb with Ruby on Rails
- Document Storage Gymnastics with Postgres
- Faster Operations with the JSONB Data Type in PostgreSQL
- Getting started with JSONB in Postgres
- Rob Conery - Document Storage Techniques with PostgreSQL and JSONB
- JSONB and PostgreSQL: Work Faster By Ditching Migrations
- Unleash the Power of Storing JSON in Postgres