Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


select (js->>'timestamp')::timestamp,
       (jsonb_path_query(js,'$.uses[*].id')::text)::bigint,
       (jsonb_path_query(js,'$.uses[*].capacity')::text)::numeric,
       (jsonb_path_query(js,'$.uses[*].usage')::text)::numeric,
       (jsonb_path_query(js,'$.uses[*].available')::text)::numeric,
       (jsonb_path_query(js,'$.uses[*]') ) ?& array['id','capacity','usage','available'],
       pg_typeof((jsonb_path_query(js,'$.uses[*]') ) )

from (values (
                 '{
                   "timestamp": "2022-02-04T09:55:21+00:00",
                   "uses": [
                     {
                       "id": 3,
                       "name": "Name1",
                       "capacity": 300,
                       "usage": 0,
                       "available": 300
                     },
                     {
                       "id": 4,
                       "name": "Name2",
                       "capacity": 450,
                       "usage": 120,
                       "available": 330
                     },
                     {
                       "id": 5,
                       "name": "Name3",
                       "capacity": 50,
                       "usage": 11,
                       "available": 39
                     }
                   ]
                 }'
                     ::jsonb)) v(js);


timestamp
jsonb_path_query
jsonb_path_query
jsonb_path_query
jsonb_path_query
?column?
pg_typeof


2022-02-04 09:55:21
3
300
0
300
t
jsonb

2022-02-04 09:55:21
4
450
120
330
t
jsonb

2022-02-04 09:55:21
5
50
11
39
t
jsonb


create temp table tbllogs(
    tbl_timestamp timestamp,
    id bigint,
    capacity numeric,
    usage numeric,
    available numeric
     );
create or replace
    function json_to_table(_input json) returns void as
    $$
    declare
        _input_b jsonb;
        v_cnt bigint;
    begin
        _input_b := _input::jsonb;
        if _input_b['timestamp'] is null
         or (select jsonb_path_query(_input_b,'$.uses[*]') limit 1) ?& array['id','capacity','usage','available'] is false
            then
                raise notice 'invalid json'; return;
        else
            raise notice 'all condition ok,  insert branch';
            insert into tbllogs(tbl_timestamp,id,capacity,usage,available)
            select (js->>'timestamp')::timestamp,
                (jsonb_path_query(js,'$.uses[*].id')::text)::bigint,
                (jsonb_path_query(js,'$.uses[*].capacity')::text)::numeric,
                (jsonb_path_query(js,'$.uses[*].usage')::text)::numeric,
                (jsonb_path_query(js,'$.uses[*].available')::text)::numeric
            from  (values (_input_b)) v(js) ;
            get diagnostics v_cnt = row_count;
            raise notice '% rows inserted into tbllogs', v_cnt;
            return;
        end if;
    end;
$$ language plpgsql;
select  * from json_to_table(
'{
                   "timestamp": "2022-02-04T09:55:21+00:00",
                   "uses": [
                     {
                       "id": 3,
                       "name": "Name1",
                       "capacity": 300,
                       "usage": 0,
                       "available": 300
                     },
                     {
                       "id": 4,
                       "name": "Name2",
                       "capacity": 450,
                       "usage": 120,
                       "available": 330
                     },
                     {
                       "id": 5,
                       "name": "Name3",
                       "capacity": 50,
                       "usage": 11,
                       "available": 39
                     }
                   ]
                 }'
    );


json_to_table





table tbllogs;


tbl_timestamp
id
capacity
usage
available


2022-02-04 09:55:21
3
300
0
300

2022-02-04 09:55:21
4
450
120
330

2022-02-04 09:55:21
5
50
11
39
timestamp	jsonb_path_query	jsonb_path_query	jsonb_path_query	jsonb_path_query	?column?	pg_typeof
2022-02-04 09:55:21	3	300	0	300	t	jsonb
2022-02-04 09:55:21	4	450	120	330	t	jsonb
2022-02-04 09:55:21	5	50	11	39	t	jsonb