ftisiot ideas about food and life
who talks suggestions soft json-pg

How to load JSON data in PostgreSQL®?

Published Mar 1, 2023 by in Postgresql, Json, Jsonb, Index, Query at https://ftisiot.net/postgresqljson/how-to-load-json-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

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

Francesco Tisiot

Mastodon Francesco comes from Verona, Italy and works as a Senior Developer Advocate at Aiven. With his many years of experience as a data analyst, he has stories to tell and advice for data-wranglers everywhere. Francesco loves sharing knowledge with others as a speaker and writer, and is on a mission to defend the world from bad Italian food!

Story logo

© 2023