PostgreSQL® offers two types of data types to handle JSON data, JSON
and JSONB
, and provides several different ways to extract fields from a JSON document:
- the
->
operator to extract the field as JSON - the
->
operator to extract an item from an array - the
->>
operator to extract the field as text - the
#>
operator to extract the object at a specific path as JSON - the
#>>
operator to extract the object at a specific path as text - the
[]
operator to extract the object at a specific path as JSON - the
json_extract_path
(jsonb_extract_path
forJSONB
columns) function to extract the object at a specific path as JSON
A quick overview of the methods is available in the video, scroll further for the written examples.
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
👉 Need a FREE PostgreSQL database?👈
🦀 Check Aiven’s FREE plans! 🦀
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": ["🍌", "🌶️", "🍍"]
}
]
}');
Extract a JSON field using the ->
operator
To extract a field from a JSON document, you can use the ->
operator. The id
and name
fields can be extracted with:
select
json_data -> 'id' id,
json_data -> 'name' name
from test;
Result
id | name
-----+----------------
778 | "Edward Olson"
Extract an item from a JSON array with the ->
operator
The ->
operator works with:
- text to identify a field name
- integers to identify an item within an array
Therefore the second pizza in the order can be extracted with
select
json_data -> 'pizzas' -> 1 as second_pizza
from test;
In the above query
json_data -> 'pizzas'
extracts thepizzas
field- the additional
-> 1
extracts the second pizza (index starts from0
)
Result
second_pizza
----------------------------------------------------------------------
{"pizzaName": "Margherita", "additionalToppings": ["🍌", "🌶️", "🍍"]}
(1 row)
Extract a JSON field as Text using the ->>
operator
To extract a field from a JSON document as Text, you can use the ->
operator. The id
and name
fields can be extracted with:
select
json_data ->> 'id' id,
json_data ->> 'name' order_name
from test;
Result:
id | order_name
-----+--------------
778 | Edward Olson
Extract a JSON field as a specific path using the @>
operator
The ->
operator allows only to extract “first level” fields, to extract in only one pass more nested items you can use the @>
operator.
E.g. to extract the additionalToppings
belonging to the second pizza in the order:
select
json_data #>
'{pizzas,1,additionalToppings}'
as additional_toppings_2nd_pizza
from test;
The {pizzas,1,additionalToppings}
is the concatenation of:
pizzas
: the field name1
: the second item in thepizzas
array (index starts from0
)additionalToppings
: the field name within thepizzas
Results
additional_toppings_2nd_pizza
-------------------------------
["🍌", "🌶️", "🍍"]
(1 row)
Extract a JSON field as a specific path using the []
operator (subscripting)
To get the second pizza name in the order you can use the []
operator
select
json_data['pizzas']['1']['pizzaName'] second_pizza_name
from test;
Result
second_pizza_name
-------------------
"Margherita"
(1 row)
Extract a JSON field as a specific path using the json_extract_path
function
To get the second pizza name in the order you can use the json_extract_path
(jsonb_extract_path
since the column is defined as JSONB)
select
jsonb_extract_path(json_data, 'pizzas', '1', 'pizzaName') second_pizza_name
from test;
Result
second_pizza_name
-------------------
"Margherita"
(1 row)
Review all the JSON PostgreSQL use-cases listed in the main page