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.

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