Как импортировать JSON файл в PostgreSQL?

Например, у меня есть файл customers.json, который представляет собой массив объектов (строго сформированный) и довольно простой (без вложенных объектов) вроде этого (что важно: оно уже включает идентификаторы):

[
  {
    "id": 23635,
    "name": "Jerry Green",
    "comment": "Imported from facebook."
  },
  {
    "id": 23636,
    "name": "John Wayne",
    "comment": "Imported from facebook."
  }
]

И я хочу импортировать их все в свой postgres db в таблицу customers.

Я нашел некоторые довольно сложные способы, когда я должен был импортировать его как столбец json-типа в таблицу типа imported_json и столбец с именем data с указанными там объектами, затем использовать sql для получения этих значений и вставить его в реальная таблица.

Но есть ли простой способ импортировать json в postgres, не касаясь sql?

Ответ 1

Вы можете передать JSON в оператор SQL, который извлекает информацию и вставляет ее в таблицу. Если атрибуты JSON имеют точно имя в качестве столбцов таблицы, вы можете сделать что-то вроде этого:

with customer_json (doc) as (
   values 
    ('[
      {
        "id": 23635,
        "name": "Jerry Green",
        "comment": "Imported from facebook."
      },
      {
        "id": 23636,
        "name": "John Wayne",
        "comment": "Imported from facebook."
      }
    ]'::json)
)
insert into customer (id, name, comment)
select p.*
from customer_json l
  cross join lateral json_populate_recordset(null::customer, doc) as p
on conflict (id) do update 
  set name = excluded.name, 
      comment = excluded.comment;

Новые клиенты будут вставлены, существующие будут обновлены. "Волшебная" часть - это json_populate_recordset(null::customer, doc), которая генерирует реляционное представление объектов JSON.

Вышеприведенное определение таблицы выглядит следующим образом:

create table customer 
(
  id        integer primary key,
  name      text not null,
  comment   text
);

Если данные предоставлены в виде файла, вам нужно сначала поместить этот файл в какую-нибудь таблицу в базе данных. Что-то вроде этого:

create unlogged table customer_import (doc json);

Затем загрузите файл в одну строку этой таблицы, например. используя команду \copy в psql (или что-то, что предлагает ваш клиент SQL):

\copy customer_import from 'customers.json' ....

Затем вы можете использовать вышеуказанный оператор, просто удалите CTE и используйте промежуточную таблицу:

insert into customer (id, name, comment)
select p.*
from customer_import l
  cross join lateral json_populate_recordset(null::customer, doc) as p
on conflict (id) do update 
  set name = excluded.name, 
      comment = excluded.comment;

Ответ 2

Оказывается, существует простой способ импортировать многострочный объект JSON в столбец JSON в базе данных postgres с помощью инструмента командной строки psql без необходимости явного встраивания JSON в оператор SQL. Техника описана в документации postgresql, но она немного скрыта.

Хитрость заключается в том, чтобы загрузить JSON в переменную psql с помощью обратных галочек. Например, учитывая многострочный файл JSON в /tmp/test.json, например:

{
  "dog": "cat",
  "frog": "frat"
}

Мы можем использовать следующий SQL для загрузки его во временную таблицу:

sql> \set content 'cat /tmp/test.json'
sql> create temp table t ( j jsonb );
sql> insert into t values (:'content');
sql> select * from t;

который дает результат:

               j                
────────────────────────────────
 {"dog": "cat", "frog": "frat"}
(1 row)

Вы также можете выполнять операции с данными напрямую:

sql> select :'content'::jsonb -> 'dog';
 ?column? 
──────────
 "cat"
(1 row)

Под прикрытием это просто встраивание JSON в SQL, но гораздо проще позволить psql выполнить саму интерполяцию.

Ответ 3

Самый простой способ импортировать JSON из файла не импортировать ни одного JSON из файла а точнее один столбец csv: список jsons из одной строки:

data.json:

{"id": 23635,"name": "Jerry Green","comment": "Imported from facebook."}
{"id": 23636,"name": "John Wayne","comment": "Imported from facebook."}

тогда под psql:

create table t ( j jsonb )
\copy t from 'd:\path\data.json'

Одна запись для каждого json (строки) будет добавлена в таблицу t.

Импорт "\ copy from" был сделан для csv, и как таковой загружает данные построчно. В результате чтение одного JSON на строку а не один массив JSON для последующего разделения, не будет использовать какую-либо промежуточную таблицу.

Более того, вы не достигнете ограничения максимального размера входной строки, которое возникнет, если ваш входной json файл слишком большой.

Таким образом, я сначала преобразовал бы ваш ввод в один столбец csv, а затем импортировал его с помощью команды copy.

Ответ 4

Что если JSON выглядит так

[ { "id":"1", "name":"abc_xyz", "language":"English", "title":"facebook", "description":"This is a test", "categories":[ "https://facebook/category/28", "https://facebook/category/29", "https://facebook/category/30", "https://facebook/category/31" ] }, "id":"2", "name":"abc_xyz", "language":"French", "title":"Twitter", "description":"This is another test", "categories":[ "https://twitter/category/2", "https://twitter/category/23", "https://twitter/category/35" ] } ] Тогда как нам загрузить его в таблицу или две таблицы (1-й Таблица (скажем, TableA) будет содержать столбцы: id, name, language, description а вторая таблица будет содержать связь id and Category

Например: TableB будет содержать

ID | CatID

1 | 28

1 | 29

1 | 30 1 | 30 и т.д.