Postgres - detect change in JSONB column value from 'null' to some value across rows

Question

I have to work with a table that looks like this:

fun_table

The actual table is obviously much larger, but for sake of discussion, is there any way to elegantly query the difference between when { a: null } turned into { a: "something"? }

My instinct is to try something (probably pseudocode) like this:

select (select update_time as finish_time from fun_table where json_data -> 'a' != 'null' as tableA) 
 - (select update_time as start_time from fun_table where json_data -> 'a' = 'null as tableB) 
from tableA, tableB

But there are a lot of different JSON objects in the actual json_data..."b", "c", etc. So again, is there a better / cleaner way to find the difference between the timestamps when "a" turned from "null" to "something"?

• Evan Carroll: No screen shots of text, ever.
• Vérace: Check here (see accepted answer)!

Answer 1

JSONB supports equality checks. So, if you're using it, you need only

1. Create an AFTER trigger on

   CREATE TRIGGER foo
    AFTER UPDATE OF json_data
    ON myTable
    FOR EACH ROW
    WHEN (new.json_data IS DISTINCT FROM old.json_data)
    EXECUTE PROCEDURE myFunction;
   

2. Either,

   • Insert the id, and new.update_time into a new table, or
   • Update the update_time setting it the current time, and query later for update_time> last_time_ran.