PostgreSQL: Calculated field in JSON

Question

Is there a way to calculate a field that is in JSON. Here is how my table is setup:

CREATE TABLE test1
(
     id int not null,
     data JSONB
)

Here is the document I am inserting

{
     'name': "test name",
     'age': 46,
     'heartrate': 211,
     'heartrateminuseage': function(){ return this.heartrate - this.age; }
}

How can I make something like this work?

Answer 1

Let's assume that you have data like:

CREATE TABLE test1(id INT NOT NULL, data JSONB);

INSERT INTO test1(id, data)
VALUES (1,'{"name": "test name",
            "age": 46,
            "heartrate": 211}'),
      (2,'{"name": "test name2",
           "age": 50,
           "heartrate": 200,
           "heartrateminuseage": 1000}');   

To calculate "heartrateminuseage" you could use extract values first and set using jsonb_set:

SELECT id, data
   ,jsonb_set(data,'{"heartrateminuseage"}',
              to_jsonb((data->>'heartrate')::int - (data ->>'age')::int),
              true) AS data_2
FROM test1;

Output:

As for calculated column:

1. Create a view

  CREATE VIEW wv_test1
  AS
    SELECT id
      ,jsonb_set(data,'{"heartrateminuseage"}',
                 to_jsonb((data->>'heartrate')::int - (data ->>'age')::int),
                 true) AS data
    FROM test1;  

2. Create a trigger that will update column
3. Create a function

More info: Computed / calculated columns in PostgreSQL and Store common query as column?