2

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?

kyle
  • 691
  • 1
  • 7
  • 17
DenaliHardtail
  • 27,362
  • 56
  • 154
  • 233

1 Answers1

0

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
FXD
  • 1,960
  • 1
  • 6
  • 9