'Insert json to db

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

UPDATE: I'm getting all values, but inserting only 1 record. Trying to insert multiple rows/record.

insert into logging.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 BIGINT)) from json_populate_recordset(NULL::logging.events, '[{
"timestamp": 1298734,
"message": "START RequestId: bjn937645",
"ingestiontime": 123456
}, {
"timestamp": 1298734,
"message": "START RequestId: bjn937645",
"ingestiontime": 123456
}]' :: jsonb) AS a

I tried using json_populate_recordset. Error out: function json_populate_recordset(reportinglogging.events, jsonb) does not exist..

jsonpostgresqlcommand-line-interface


Solution 1:[1]

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

Solution 2:[2]

create or replace  function events_insert(p_object jsonb)
returns setof text AS
$body$
    declare
    p_json_value jsonb;
    v_rec record;
    v_timestamp timestamp;
    v_message text;
    v_time time WITHOUT TIME ZONE;
    v_id bigint;
    begin
    if (p_object['events'] ) is null then raise exception 'no working';
    else
        p_json_value := (p_object)['events'];
        raise info 'p_json_value: %', p_json_value::text;
        raise info 'p_json_value type: %', pg_typeof(p_json_value);
        for v_rec in (select  (jsonb_each_text (jsonb_array_elements
                                                    (p_json_value))).* )
        loop
            case
            when v_rec.key = 'timestamp' then v_timestamp := to_timestamp(v_rec.value::bigint);
            when v_rec.key = 'message' then v_message := v_rec.value;
            when v_rec.key = 'ingestiontime'
                then v_time :=  cast(to_char(v_rec.value::int,'FM999:99:99')as time WITHOUT TIME ZONE);
            else null;
            end case;
        end loop;
        insert into events(timestamp,message,ingestion_time)
                values(v_timestamp,v_message,v_time) returning id into v_id;
        return query select row_to_json(e.*)::text from events e where id = v_id;
    end if;
    end
$body$
language plpgsql;

call it:

select * from events_insert(
'{"events":
      [{"timestamp": 1298734,
      "message": "START RequestId: bjn937645", 
        "ingestiontime": 123456}]}'::jsonb);

demo

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Edouard
Solution 2 Mark

Related Questions