REG: Json field issue

[Anonymous]

Hi Team,

I would like to share here, the issue am facing almost couple of days.

I have 10000 json files with same structure need to be migrated to postgres. my schema structure is this 

{
"absolute.acceleration": 0,
"ain.1": 0,
"ain.2": 0,
"ain.3": 0,
"ain.4": 0,
"alarm.event": false,
"alarm.mode.status": false,
"battery.voltage": 4.243,
"brake.acceleration": 0,
"bump.acceleration": 0,
"channel.id": 123258,
"device.temperature": 39,
"engine.ignition.status": true,
"external.powersource.voltage": 25.452,
"external.powersource.voltage.range.outside.status": false,
"geofence.status": false,
"gnss.antenna.status": true,
"gnss.type": "glonass",
"gsm.signal.level": 33,
"gsm.sim.status": false,
"ibutton.code.1": "0",
"ibutton.connected.status": false,
"ident": "86671220037899538",
"incline.event": false,
"internal.battery.voltage.limit.lower.status": false,
"internal.bus.supply.voltage.range.outside.status": false,
"movement.status": false,
"peer": "183.17122.77.64:5229382",
"position.altitude": 64,
"position.direction": 178.9,
"position.hdop": 0.5,
"position.latitude": 2.765196,
"position.longitude": 101.830418,
"position.satellites": 12,
"position.speed": 0,
"position.valid": true,
"protocol.id": 16,
"record.seqnum": 4568,
"server.timestamp": 156231388814.759614,
"shock.event": true,
"timestamp": 1568381471,
"turn.acceleration": 0,
"user.data.value.1": 0,
"user.data.value.2": 13502043,
"user.data.value.3": 0,
"user.data.value.4": 0,
"user.data.value.5": 0,
"user.data.value.6": 0,
"user.data.value.7": 0,
"user.data.value.8": 0
}

First issue : i couldn't create this fields, shows error : why a fieldname with ( ", number, absolute.expression ) cannot be created .. how can i do this ?   

Second issue : i try using metadata section to import the json, please see the screenshot. 

Thanks,

Prabuj

[Sujay1]

Hi Prabuj,

    I was able to extract your json using both tjsonfileinput and also using context. below is the way to define your fields. The trick is to surround the dotted names with '[]'

acceleration: "['absolute.acceleration']"
event: "['alarm.event']"

and so on. Let me know if this is what your looking for. happy to help.

Also attaching the screenshots of the same.

Regards,

Sujay.