I am trying to merge together the 2 duplicates entries in the attached screenshot. The primary key in this table is (email_address, census_key).
The last column type is jsonb and my expected final entry should look like in the following screenshot:
Wherever the keys are overlapping, the latest timestamp should be kept. Also, different keys should be merged together.
This is the table definition:
CREATE TABLE public.test
email_address character varying(256) NOT NULL,
census_key character varying(32) NOT NULL,
doc jsonb NOT NULL
These are the insert queries that I've run:
INSERT INTO public.test(
email_address, census_key, doc)
VALUES ('test', 'test_key', '{"jsonmap":{"1": 1526899328, "2":
1526800040}}');
INSERT INTO public.test(
email_address, census_key, doc)
VALUES ('TEST', 'test_key', '{"jsonmap":{"1": 1526888354, "3":
1521123440}}');
Is there a way of doing this in a PSQL query? Can you please point me in the right direction?
Many, many thanks!
