Where? What? Who? - Everything in one place

Working with PostgreSQL JSONB

PostgreSQL

PostgreSQL in version 9.4 introduced 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:

  • -> returns jsonb object field or array by key
  • ->> returns jsonb 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 Pece 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

 

Written by Stefan Georgievski, SDE at HASELT.