Как загрузить файл JSON в Postgres со всеми его ключами?

У меня есть файл XML, который разбит на более мелкие таблицы. Я могу загрузить их в Navicat, все отлично, кроме одной таблицы. Вот структура XML:

<Food> 
  <Id> 100 </Id>
  <Type> Meat </Type>
  <Expiry Date>
    <Chicken>
      2020/12/20
    </Chicken>
    <Beef>
      2020/12/25
    </Beef>
  </Expiry Date>
</Food>

<Food>
  <Id> 200 </Id>
  <Type> Vegetables </Type>
  <Nutrition> B1 </Nutrition>
</Food>

Я превратил его в JSON, используя xmltodict в Python:

[{
"Id": "100",
"Type": "Meat",
"Expiry Date": {
  "Chicken": "2020/12/20",
  "Beef": "2020/12/25"
  }
},

{
"Id": "200",
"Type": "Vegetables",
"Nutrition": "B1"
}]

Однако, когда я загружаю этот файл JSON в Navicat (соединение PostgresSQL), схема таблицы SQL имеет только идентификатор, тип и дату истечения срока действия. Как видите, ключи отсутствуют в одном объекте, но появляются в других. Как я могу создать таблицу SQL со всеми полями из файла JSON? (Идентификатор, тип, срок годности и питание).


postgresql json sql navicat
person Hana    schedule 14.12.2020    source источник
comment
Проанализируйте JSON на стороне клиента и отправьте соответствующие операторы INSERT в базу данных. В качестве альтернативы используйте json_array_elements и оператор ->>, чтобы сделать это в базе данных.   -  person Laurenz Albe    schedule 14.12.2020

Ответы (2)


arrow_upward
1
arrow_downward

Если у вас нет особой причины сначала преобразовать его в JSON, вы можете использовать XMLTABLE с «исходным» встроенным XML следующим образом:

select * 
from xmltable ( '//Food' passing 
    xmlparse (document '<dummyRoot>
    <Food> 
      <Id> 100 </Id>
      <Type> Meat </Type>
      <ExpiryDate>
        <Chicken>
          2020/12/20
        </Chicken>
        <Beef>
          2020/12/25
        </Beef>
      </ExpiryDate>
    </Food>
    <Food>
      <Id> 200 </Id>
      <Type> Vegetables </Type>
      <Nutrition> B1 </Nutrition>
    </Food>
    </dummyRoot>')
  columns 
   "Id" integer,
   "Type" text, 
   "ExpiryDate.Chicken" date path 'ExpiryDate/Chicken',
   "ExpiryDate.Beef" date path 'ExpiryDate/Beef',
   "Nutrition" text
);

‹Expiry Date› и ‹/Expiry Date› необходимо изменить на ‹ExpiryDate› и ‹/ExpiryDate› соответственно, чтобы имена тегов стали действительными. Вот результат:

Id |Type        |ExpiryDate.Chicken|ExpiryDate.Beef|Nutrition|
---|------------|------------------|---------------|---------|
100| Meat       |        2020-12-20|     2020-12-25|         |
200| Vegetables |                  |               | B1      |

Изменить Упрощенный XML-запрос

Если JSON требуется, то, как предложил Laurenz Albe:

select 
    (j->>'Id')::integer id, 
    j->>'Type' "type", 
    (j->'Expiry Date'->>'Chicken')::date xdate_chicken, 
    (j->'Expiry Date'->>'Beef')::date xdate_beef,
    j->>'Nutrition' nutrition
from jsonb_array_elements
('[{
"Id": "100",
"Type": "Meat",
"Expiry Date": {
  "Chicken": "2020/12/20",
  "Beef": "2020/12/25"
  }
},
{
"Id": "200",
"Type": "Vegetables",
"Nutrition": "B1"
}]') j;
person Stefanov.sm    schedule 14.12.2020
comment
Большое тебе спасибо. Однако XML-файл содержит гораздо больше элементов, чем этот, и узлы в пределах даты истечения срока действия также намного больше и непредсказуемы. Кроме того, есть более 5 элементов, похожих на Expiry Date. Как я могу добиться того же самого без необходимости вручную перечислять все столбцы? - person Hana; 15.12.2020
comment
Ну, боюсь, что не знаю. Однако вы можете использовать json_object_keys для извлечения списка имен элементов, а затем выполнить некоторую работу с текстовым редактором, чтобы упростить перечисление всех столбцов без (или меньше?) ошибок. - person Stefanov.sm; 15.12.2020
comment
И кстати, если структура Expiry Date является динамической, то почему вы не используете ее в виде столбца JSON (B) в таблице? Это может быть не самая удобная вещь в использовании, но может быть лучшей из доступных, по крайней мере, вы не потеряете данные. - person Stefanov.sm; 15.12.2020
comment
О, это именно то, что я придумал несколько минут назад, хахаха. Однако, если я просто загружу точный файл JSON в Postgres, у меня будет тип jsonb, но я потеряю поле питания. - person Hana; 15.12.2020
comment
Ну почему не гибридная структура, обычные столбцы (типа id, type, Nutrition) и структурированные в JSON (типа expiry_date,...)? Это даст вам лучшее из обоих миров, то есть ключи и детали. - person Stefanov.sm; 15.12.2020

arrow_upward
0
arrow_downward

Я нашел решение для этого. Используя Python, я читаю файл XML, чтобы получить все возможные имена столбцов, затем внутри каждого имени столбца я читаю его подэлементы и записываю их как jsonb в Postgres.

person Hana    schedule 24.12.2020