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

- 3 minutes read - 464 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

 

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

⚡️ Need to optimize your SQL query with AI? ⚡️
🐧 Check Aiven AI database optimizer! Powered by EverSQL 🐧

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