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

user_19441666 0
Viewed 35

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 :)

postgresql json
1 Answers
  • 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;
user_156034770
Related