将 JSON 文件导入 Windows 10 上的 Postgresql 11 [英] Importing a JSON file into Postgresql 11 on Windows 10

问题描述

我有一个 JSON 文件，C:\sensors\201802091904.json.此数据文件与 PostgreSQL 安装位于同一本地驱动器上.

以下是 JSON 示例:

<预><代码>[{"Data": " ** 属性值对集合 ** }} ", "EventId": "", "Timestamp": ""},{"Data": " ** 属性值对集合 ** }} ", "EventId": "", "Timestamp": ""},{"Data": " ** 属性值对集合 ** }} ", "EventId": "", "Timestamp": ""}]

Data 属性值是一个 JSON 对象，包含一个观察值以及关于传感器的数据.

我想将文件内容加载到 PostgreSQL 11 表中.我想将 Data 属性值加载到 JSON 列类型中.如果可能，我想将元属性(如 EventId 和 Timestamp)加载到它们自己的列中，但我可以稍后弄清楚.

我找到了这个示例，但它没有产生我们想要的结果.整个 JSON 块以单个字段结束.我们需要每个 JSON 对象，{"Data": " ** 属性值对集合 ** }} ", "EventId": "", "Timestamp": ""}，插入到它自己的行.

这是迄今为止最成功的尝试:

XXX =# \set content `type C:\sensors\201802091904.json`XXX =# 创建表 t2 ( j jsonb );XXX =# 插入 t2 值 (:'content');XXX =#从t2中选择*；

如何在 Windows 10 上将 JSON 文件加载到 PostgreSQL 11?

解决方案

如果你成功地遵循了这个例子，你离将你的 jsonb 数组扩展为几个简单的 jsonb 就差 1 步了:

CREATE TABLE T (j jsonb);插入 T 选择 jsonb_array_elements(j)::jsonb 从 T2

I have a JSON file, C:\sensors\201802091904.json. This data file is on the same local drive as the PostgreSQL installation.

Here is a sample of the JSON:

[
{"Data": " ** collection of property-value pairs ** }} ", "EventId": "", "Timestamp": ""},
{"Data": " ** collection of property-value pairs ** }} ", "EventId": "", "Timestamp": ""},
{"Data": " ** collection of property-value pairs ** }} ", "EventId": "", "Timestamp": ""}
]

The Data property value is a JSON object and contains an observation as well as data about the sensor.

I want to load the contents of the file into a PostgreSQL 11 table. I'd like to load the Data property value into a JSON column type. If possible, I'd like to load the meta properties, like EventId and Timestamp into their own columns, but I can figure that out later.

I found this example but it hasn't produced the results we seek. The entire chunk of JSON ends up in a single field. We need each JSON object, {"Data": " ** collection of property-value pairs ** }} ", "EventId": "", "Timestamp": ""}, inserted into its own row.

Here's the most successful attempt so far:

XXX =# \set content `type C:\sensors\201802091904.json`
XXX =# create table t2 ( j jsonb );
XXX =# insert into t2 values (:'content');
XXX =# select * from t2;

How do I load a JSON file into PostgreSQL 11 on Windows 10?

解决方案

If you have followed the example successfully, you are 1 step away from expanding your jsonb array to several simple jsonb:

CREATE TABLE T (j jsonb);
INSERT INTO T SELECT jsonb_array_elements(j)::jsonb FROM T2

这篇关于将 JSON 文件导入 Windows 10 上的 Postgresql 11的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！