Insert json to db-CodePudding

I'm trying to insert json data into db. Sample json:

{ 
"events": 
  [{                                                                                                   
     "timestamp": 1298734,                                                                                               
     "message": "START RequestId: bjn937645",                                                    
     "ingestiontime": 123456                                                                       
  }]
}

Table:

Create table events(id, timestamp, message, ingestion_time)

I can get the timestamp and message values but ingestion_time comes in as NULL. I'm trying to get the nested values and convert the epoch timestamp to datetime. Thanks

CodePudding user response：

insert into events (timestamp, message, ingestion_time)
select a.json#>>'{events,0,timestamp}', a.json#>>'{events,0,message}', to_timestamp(cast(a.json#>>'{events,0,ingestiontime}' AS integer))
  from (select
'{ 
"events": 
  [{                                                                                                   
     "timestamp": 1298734,                                                                                               
     "message": "START RequestId: bjn937645",                                                    
     "ingestiontime": 123456                                                                       
  }]
}' :: json AS json) AS a

see test in dbfiddle