如何将JSON文件及其所有密钥加载到Postgres中

Question

我有一个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>

我在Python:中使用xmltodict将其转换为JSON

[{
"Id": "100",
"Type": "Meat",
"Expiry Date": {
"Chicken": "2020/12/20",
"Beef": "2020/12/25"
}
},
{
"Id": "200",
"Type": "Vegetables",
"Nutrition": "B1"
}]

然而，当我将这个JSON文件加载到Navicat(PostgresSQL连接(上时，SQL表模式只有Id、Type和Expiry Date。正如您所看到的，一个对象中缺少关键点，但其他对象中出现了关键点。如何创建一个包含JSON文件中所有字段的SQL表？(Id、类型、有效期和营养(。

Answer 1

如果您没有特殊的理由首先将其转换为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
);

<到期日期>并且<到期日期>需要改变为<到期日期>并且<到期日期>以成为有效的标签名称。这就是结果：

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;

Answer 2

我已经找到了解决方案。使用Python，我读取XML文件以获得所有可能的列名，然后在每个列名中，我读取其子元素，并在Postgres中将其写成jsonb。