2

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"?

2
  • 1
    No screen shots of text, ever. Sep 26, 2017 at 22:00
  • 1
    Check here (see accepted answer)!
    – Vérace
    Sep 27, 2017 at 7:29

1 Answer 1

-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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.