Импорт JSON файла в PostgreSQL: простой и эффективный способ

Быстрый ответ

Для импорта JSON-файла в PostgreSQL следует использовать команду COPY и тип данных jsonb. Создайте в своей базе данных таблицу с столбцом jsonb, после чего реализуйте следующие операции:

CREATE TABLE IF NOT EXISTS json_data_table (
  data jsonb
);

COPY json_data_table(data) FROM '/path/to/jsonfile.json';

Поменяйте json_data_table на название вашей таблицы и /path/to/jsonfile.json на актуальный путь до файла JSON. В результате содержание JSON-файла будет импортировано в базу данных PostgreSQL в качестве объектов jsonb.

Повышение уровня мастерства :arrow_up:

Если вы стремитесь усовершенствоваться в импортировании JSON-файлов в PostgreSQL, изучите следующие методы:

Как выглядят проблемы с вложенными JSON-объектами?

Организуйте временную таблицу для обработки вложенных структур JSON:

CREATE TABLE staging_table (
  id serial PRIMARY KEY,
  raw_data jsonb
);

COPY staging_table(raw_data) FROM '/path/to/jsonfile.json';

INSERT INTO target_table (column1, column2)
SELECT raw_data->>'key1', raw_data->'nested'->>'key2'
FROM staging_table;

Подробнее об этом расскажет наш спикер на видео

Упрощение работы с JSON-данными

Преобразуйте JSON в удобные для работы реляционные данные с применением функции json_populate_recordset:

CREATE TABLE json_records AS
SELECT * FROM json_populate_recordset(null::your_table_type, (SELECT raw_data FROM staging_table));

Осуществление операции upsert с данными JSON

Если JSON включает информацию о уже существующих записях, используйте команду ON CONFLICT:

INSERT INTO target_table (id, data)
SELECT * FROM json_populate_recordset(null::target_table, '{"id": 1, "data": "new"}')
ON CONFLICT (id) DO UPDATE SET
data = EXCLUDED.data;

Использование оператора общего табличного выражения (CTE)

Усовершенствуйте процесс импорта JSON с помощью объекта CTE:

WITH preprocessed_json AS (
  SELECT id, data
  FROM json_populate_recordset(null::target_table, (SELECT raw_data FROM staging_table))
)
INSERT INTO target_table (id, data)
SELECT id, data FROM preprocessed_json
ON CONFLICT (id) DO NOTHING;

Увеличение скорости запросов с использованием JSONB

Применяйте тип jsonb для оптимизации работы с данными:

ALTER TABLE target_table ALTER COLUMN data TYPE jsonb USING data::jsonb;

Визуализация

Подумайте о процессе импорта JSON-данных в PostgreSQL как о аккуратном размещении одежды (👕👖) в шкафах (🗄):

Загрузка JSON:        Разбор в PostgreSQL:
🧳 { "clothes":     🗄️ Таблица 'clothing':
    [               [
      "👕",            { "item": "shirt" },
      "👖"             { "item": "pants" }
    ]               ]
  }

Также как и одежда, вам нужно организоваться, чтобы все было упорядочено. Аналогичным образом, данные в PostgreSQL требуют аккуратной структуризации.

Профессиональные советы и приемы

Использование мета-команд

Мета-команды в оболочке команд PostgreSQL psql, такие как \set и \copy, служат для управления файлами и выполнения SQL-сценариев:

\set content `cat /path/to/jsonfile.json`
INSERT INTO json_data_table (data) VALUES (:'content');

Работа с большими файлами

Если ваш JSON-файл внушительный по размеру, упростите процедуру, применяя формат CSV:

\copy large_json_data_table (data) FROM '/path/to/large_jsonfile.json' (FORMAT csv);

Ускорение обработки JSON

С привлечением утилит jq и spyql, преобразуйте JSON в SQL инструкции для быстрой вставки данных:

cat /path/to/jsonfile.json | jq '...' | spyql '...' | psql -c "COPY (FIELD LIST) FROM STDIN";

Полезные материалы

1. PostgreSQL: Documentation: COPY — официальная документация PostgreSQL на команду COPY.
2. GitHub – lukasmartinelli/pgfutter: Import into PostgreSQL the easy way — инструмент для упрощения импорта данных в форматах JSON и CSV.
3. Tutorial | DigitalOcean — руководство по использованию мощной функциональности типа данных JSONB в PostgreSQL.
4. Tutorial: Use Python and SQL to load data into Postgres — инструкция по импорту больших объемов данных в PostgreSQL с применением Python.
5. PostgreSQL: Documentation: JSON Functions and Operators — детальное описание функций и операторов JSON в PostgreSQL.
6. Medium — способы осуществления быстрого импорта JSON в PostgreSQL с привлечением Python и psycopg2.