How to load JSON data in PostgreSQL®?

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 and JSONB 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.

👉 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

If you need to load JSON files in an existing table with well defined columns, check the dedicated article on how to load the JSON Data with PostgreSQL COPY and jq.

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';