Adding a parent attribute to each element of a nested array in JSON with Postgres-CodePudding

Good Morning,

I receive the following JSON-Format via PostgREST:

{
  "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
    }
  ]
}

I would like to write a function in PostgreSQL that automatically writes the data from the nested array into a table.

Example:

INSERT INTO tblLogs (timestamp, id, capacity, usage, available) VALUES("2022-02-04T09:55:21 00:00", 3, 300, 0, 300)
INSERT INTO tblLogs (timestamp, id, capacity, usage, available) VALUES("2022-02-04T09:55:21 00:00", 4, 450, 120, 330)
...

I don't know how i map the attribute "timestamp" to each item of the nested array.

Please help, i'm a Postgres-Newbie :)

CodePudding user response：

You need to know jsonb_path_query, plpgsql conditional branch.
The logic is like a function take json input, do computation: decompose the json, insert to the relevant table. then the function return nothing.
json is less structure type, you need to enforce that input json meet certain criteria.In this context is that we need certain json key exists.
demo

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;