PostgreSQL® offers two types of data types to handle JSON data, JSON
and JSONB
. This doc showcases how to load JSON data into a column.
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
A quick overview of the methods is available in the video, scroll further for the written examples.
👉 Need a FREE PostgreSQL database? check Aiven's free plans👈
Load JSON data in PostgreSQL
The load a JSON document in a JSON
or JSONB
column in PostgreSQL you need to include it as string. E.g. to include the following JSON:
{
"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": ["🍌", "🌶️", "🍍"]
}
]
}
In a table containing a json_data
JSONB
column:
create table test(id serial, json_data jsonb);
You can just insert the json_data
column as string:
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": ["🍌", "🌶️", "🍍"]
}
]
}');
PostgreSQL will validate the content as JSON and, if correct, store the data.
To check an example of how to load a set of JSON in a PostgreSQL table, check the Indian restaurant story
NOTE: If you directly want to load a file with the
\copy
command, containing any\
characters, check out the StackOverflow thread.
TLDR:\copy <TABLE_NAME> from program 'sed -e ''s/\\/\\\\/g'' <FILE_NAME>.json';