How to edit a JSON object in PostgreSQL®?

- 3 minutes read - 444 words

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the jsonb_set function to remove the null values for JSONB columns.

NOTE: To review the differences between JSON and JSONB check out the related article.

NOTE: more info is available in the PostgreSQL JSON functions documentation page

 

👉 Want a FREE PostgreSQL database?👈
🦀 Check Aiven’s FREE plans! 🦀

⚡️ Want to optimize your SQL query with AI? ⚡️
Check Aiven SQL Query Optimizer!

⚡️ Want a Fully PostgreSQL optimized Database? ⚡️
Check Aiven AI Database Optimizer!
Powered by EverSQL, it provides you index and SQL rewrite recommendations to take your database performance to the next level

The dataset

The dataset is the following:

{
    "id": 778,
    "shop": "Luigis Pizza",
    "name": "Edward Olson",
    "phoneNumbers":
        ["(935)503-3765x4154","(935)12345"],
    "address": "Unit 9398 Box 2056\nDPO AP 24022",
    "image": null,
    "pizzas": [
        {
            "pizzaName": "Salami",
            "additionalToppings": ["🥓", "🌶️"]
        },
        {
            "pizzaName": "Margherita",
            "additionalToppings": ["🍌", "🌶️", "🍍"]
        }
    ]
}
Check out the description of the fields The following examples use a pizza order dataset with an order having:
  • id: 778
  • shop: “Luigis Pizza”
  • name: “Edward Olson”
  • phoneNumbers:["(935)503-3765x4154","(935)12345"]
  • address: “Unit 9398 Box 2056\nDPO AP 24022”
  • image: null
  • and two pizzas contained in the pizzas item:
[
    {
        "pizzaName": "Salami",
        "additionalToppings": ["🥓", "🌶️"]
    },
    {
        "pizzaName": "Margherita",
        "additionalToppings": ["🍌", "🌶️", "🍍"]
    }
]
If you want to reproduce the examples, check how to recreate the dataset

It can be recreated with the following script:

create table test(id serial, json_data jsonb);

insert into test(json_data) values (
'{
    "id": 778,
    "shop": "Luigis Pizza",
    "name": "Edward Olson",
    "phoneNumbers":
        ["(935)503-3765x4154","(935)12345"],
    "address": "Unit 9398 Box 2056\nDPO AP 24022",
    "image": null,
    "pizzas": [
        {
            "pizzaName": "Salami",
            "additionalToppings": ["🥓", "🌶️"]
        },
        {
            "pizzaName": "Margherita",
            "additionalToppings": ["🍌", "🌶️", "🍍"]
        }
    ]
}');

Edit the JSON object with the jsonb_set function

JSON objects can be edited using the jsonb_set function. To change the first pizzaName in the pizzas item to 4 Stagioni

select
    jsonb_set(
        json_data -> 'pizzas',
        '{0,"pizzaName"}',
        to_jsonb('4 Stagioni'::text), 
        false)
        as change_first_pizza_name
from test;

Result

                                                        change_first_pizza_name
----------------------------------------------------------------------------------------------------------------------------------------
 [{"pizzaName": "4 Stagioni", "additionalToppings": ["🥓", "🌶️"]}, {"pizzaName": "Margherita", "additionalToppings": ["🍌", "🌶️", "🍍"]}]
(1 row)

The json_set has the following parameters:

  1. the JSON object to edit (json_data -> 'pizzas')
  2. the path to the key to be edited ('{0,"pizzaName"}', 0 is the first pizza in the array, pizzaName is the key)
  3. the new value (to_jsonb('4 Stagioni'::text))
  4. boolean to create field if missing (false)

NOTE: The jsonb_set_lax function has the same output of jsonb_set for non null values. For null values, it changes the behaviour depending on a 5th parameter (null_value_treatment) that can be:

  • raise_exception
  • use_json_null
  • delete_key
  • return_target

Review all the JSON PostgreSQL use-cases listed in the main page