How to remove fields from a JSON document in PostgreSQL®?

- 3 minutes read - 492 words

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides two different ways to remove fields from a JSON document:

  • the - operator to remove first level fields
  • the #- operator to remove specific path

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": ["🍌", "🌶️", "🍍"]
        }
    ]
}');

Remove first level fields from JSON with the - operator

First level fields can be removed from a JSON document using the - operator. To remove the pizzas and id fields from the pizza order

select 
    json_data 
        - ARRAY['pizzas','id']
        as no_pizzas_and_id
from test;

Result

                                                                           no_pizzas_and_id
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"name": "Edward Olson", "shop": "Luigis Pizza", "image": null, "address": "Unit 9398 Box 2056\nDPO AP 24022", "phoneNumbers": ["(935)503-3765x4154", "(935)12345"]}
(1 row)

Remove specific path fields from JSON with the -# operator

Specific path fields can be removed from a JSON document using the -# operator. To remove the additionalToppings from the second pizza (index starts from 0) in the pizzas item

select 
    json_data 
       #- '{pizzas,1,additionalToppings}'
       as no_2nd_pizza_additionalToppings
from test;

Result

                                                                                                                           no_2nd_pizza_additionaltoppings
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id": 778, "name": "Edward Olson", "shop": "Luigis Pizza", "image": null, "pizzas": [{"pizzaName": "Salami", "additionalToppings": ["🥓", "🌶️"]}, {"pizzaName": "Margherita"}], "address": "Unit 9398 Box 2056\nDPO AP 24022", "phoneNumbers": ["(935)503-3765x4154", "(935)12345"]}
(1 row)

The '{pizzas,1,additionalToppings}' is a JSON path identifying the pizzas field, the 2nd entry in the array (1 since the index starts from 0) and the nested additionalToppings field.

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