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
andJSONB
check out the related article.
NOTE: more info is available in the PostgreSQL JSON functions documentation page
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
: 778shop
: “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