Postgresql – Postgres – detect change in JSONB column value from ‘null’ to some value across rows

jsonpostgresql

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

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

Best Answer

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

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;

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.

Related Solutions

PostgreSQL – How to Return Value for Key from JSONB Column

This post gives you your answer:

CREATE TABLE json_test 
(
  id serial primary key,
  data jsonb
);
INSERT INTO json_test (data) VALUES ('{"0":"INVALID","1":"BELOW","2":"EQUAL","3":"ABOVE"}');

You specify the element that you wish to extract thus:

SELECT id, data->>'1' AS whatever FROM json_test;

And the result is:

id      whatever
1       BELOW

There are a shedload of new json functions in PostgreSQL - there could be more examples (hint PostgreSQL team...).

How to Insert Values with Default Values from a Select Query in PostgreSQL

If you add all ColumnsNames (exclude the 'id' Column) after the Tablename, there will be Insert the serial automaticly like:

INSERT INTO animals(nm,typ,tvi,tvf) select nm,typ,tvi,tvf from json_po.....

You can also add a DEFAULT Value in your Column, to set a Default Value if the column is not in the Insert Column-list.

Best Answer

Related Solutions

PostgreSQL – How to Return Value for Key from JSONB Column

How to Insert Values with Default Values from a Select Query in PostgreSQL

Related Question