How to check if JSON contains in PostgreSQL®?

- 3 minutes read - 506 words

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides several different ways to check if a value/field from a JSON document:

  • the @> operator to check if a JSON is contained
  • the ? operator to check if a field name is contained
  • the ?| operator to check if any field name in the array is contained
  • the ?& operator to check if all the field names in the array are contained

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

Check if a JSON is contained with the @> operator

To check if a JSON object {"id": 778} is contained, you can use

select 
    json_data 
        @> '{"id": 778}'::jsonb  
        as contains_id
from test;

Result

 contains_id
-------------
 t
(1 row)

Check if a field name is contained with the ? operator

To check if the shop field

select 
    json_data ? 'shop'
        as contains_shop
from test;

Result

 contains_shop
---------------
 t
(1 row)

Check if any field name in the array is contained with the ?| operator

To check if the JSON object contains any of the shop and cookies fields

select 
    json_data 
        ?| ARRAY['shop','cookies']
        as contains_shop_or_cookies
from test;

Result

 contains_shop_or_cookies
--------------------------
 t
(1 row)

Check if all the field names in the array are contained the ?& operator

To check if the JSON object contains both the shop and cookies fields

select 
    json_data 
        ?& ARRAY['shop','cookies']
        as contains_shop_or_cookies
from test;

Result

 contains_shop_or_cookies
--------------------------
 f
(1 row)

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