PostgreSQL® offers two types of data types to handle JSON data, JSON
and JSONB
, you can use several functions to extract the keys and related values in tabular format.
json_each
(jsonb_each
for the JSONB type) to extract the key as text and the value as separate JSON objectjson_each_text
(jsonb_each_text
for the JSONB type) to extract the key as text and the value as separate text objectjson_object_keys
(jsonb_object_keys
for the JSONB type) to extract the keys
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 the key as text and the value as JSON object with the json_each
function
The keys and related values can be extracted with the json_each
(jsonb_each
since the column is defined as JSONB) function
select p.*
from test
cross join lateral jsonb_each(json_data) p;
Result
key | value
--------------+------------------------------------------------------------------------------------------------------------------------------------
id | 778
name | "Edward Olson"
shop | "Luigis Pizza"
image | null
pizzas | [{"pizzaName": "Salami", "additionalToppings": ["🥓", "🌶️"]}, {"pizzaName": "Margherita", "additionalToppings": ["🍌", "🌶️", "🍍"]}]
address | "Unit 9398 Box 2056\nDPO AP 24022"
phoneNumbers | ["(935)503-3765x4154", "(935)12345"]
(7 rows)
Extract the key as text and the value as text object with the json_each_text
function
The keys and related values can be extracted with the json_each_text
(jsonb_each_text
since the column is defined as JSONB) function
select p.*
from test
cross join lateral jsonb_each_text(json_data) p;
Result
key | value
--------------+------------------------------------------------------------------------------------------------------------------------------------
id | 778
name | Edward Olson
shop | Luigis Pizza
image | 👻
pizzas | [{"pizzaName": "Salami", "additionalToppings": ["🥓", "🌶️"]}, {"pizzaName": "Margherita", "additionalToppings": ["🍌", "🌶️", "🍍"]}]
address | Unit 9398 Box 2056 +
| DPO AP 24022
phoneNumbers | ["(935)503-3765x4154", "(935)12345"]
(7 rows)
NOTE: compared to
json_each
, thejson_each_text
sets the output of thevalue
column as text rather than JSON (or JSONB)
Extract the JSON keys with the json_object_keys
function
The keys can be extracted with the json_object_keys
(jsonb_object_keys
since the column is defined as JSONB) function
select
jsonb_object_keys(json_data)
from test;
Result
jsonb_object_keys
-------------------
id
name
shop
image
pizzas
address
phoneNumbers
(7 rows)
Review all the JSON PostgreSQL use-cases listed in the main page