SQL? – work with data from the clob::JSON column – issue SOLVED

ANSWER IS:

1. UPDATE public.product SET validity_date = to_timestamp(clob::json->’validityDate’::text #>> ‘{}’, ‘yyyy-MM-dd’)::timestamp WHERE id = ‘6331f937-1890-9600-01fd-eafd88efa78f’;
2. UPDATE public.product SET validity_date = to_timestamp(clob::json->’validityDate’::text #>> ‘{}’, ‘yyyy-MM-dd’)::timestamp WHERE person_id = ‘d887e1b2-189e-4b49-a740-9c4c251ef68b’;
3. UPDATE public.product SET person_id = clob::json->’personId’::text #>> ‘{}’ WHERE id = ‘6331f937-1890-9600-01fd-eafd88efa78f’;
4. SELECT * FROM public.product WHERE id=’6331f937-1890-9600-01fd-eafd88efa78f’;

— SELECT id, person_id, validity_date FROM public.product WHERE validity_date IS NULL;
— UPDATE public.product SET validity_date = to_timestamp(clob::json->’validityDate’::text #>> ‘{}’, ‘yyyy-MM-dd’)::timestamp WHERE validity_date IS NULL;

— SELECT id, person_id, validity_date FROM public.product WHERE person_id IS NULL;
— UPDATE public.product SET person_id = clob::json->’personId’::text #>> ‘{}’ WHERE person_id IS NULL;

—– Update sql request
— SELECT id, person_id, validity_date FROM public.product WHERE person_id IS NULL OR validity_date IS NULL ORDER BY id DESC LIMIT 5 OFFSET 0;
— EXPLAIN ANALYZE WITH rows AS (SELECT person_id FROM public.product ORDER BY id DESC LIMIT 5 OFFSET 0)
UPDATE public.product SET person_id = clob::json->’personId’::text #>> ‘{}’ WHERE EXISTS(SELECT * FROM rows WHERE rows.person_id IS NULL);

WITH filter_rows AS (SELECT id FROM public.product WHERE validity_date IS NULL OR person_id IS NULL ORDER BY id DESC OFFSET 0 LIMIT 5)
UPDATE public.product main_rows
SET validity_date = to_timestamp(main_rows.clob::json->’validityDate’::text #>> ‘{}’, ‘yyyy-MM-dd’)::timestamp,
person_id = main_rows.clob::json->’personId’::text #>> ‘{}’
FROM filter_rows
WHERE main_rows.id = filter_rows.id
RETURNING main_rows.id, main_rows.person_id, main_rows.validity_date;

ALTER TABLE public.product ADD person_id VARCHAR(255) NOT NULL UNIQUE;
ALTER TABLE public.product DROP COLUMN person_id;

SEARCH SCRIPT

SELECT * FROM public.product WHERE clob::json->’productNumber’::text #>> ‘{}’ = ‘0000003665’

=================================
MIGRATION SCRIPT:

ALTER TABLE public.product DROP COLUMN person_id;

CREATE INDEX index_person_id
ON public.product (person_id);

ALTER TABLE public.product ADD person_id VARCHAR(255);

UPDATE public.product
SET person_id = clob::json->’personId’::text #>> ‘{}’
WHERE person_id IS NULL;

ALTER TABLE public.product ADD validity_date TIMESTAMP without time zone NULL;

UPDATE public.product
SET validity_date = to_timestamp(clob::json->’validityDate’::text #>> ‘{}’, ‘yyyy-MM-dd’)::timestamp
WHERE validity_date IS NULL;

UPDATE public.product
SET person_id = clob::json->’personId’::text #>> ‘{}’,
validity_date = to_timestamp(clob::json->’validityDate’::text #>> ‘{}’, ‘yyyy-MM-dd’)::timestamp
WHERE person_id IS NULL OR validity_date IS NULL;

===========================================================================================================================================

THE ANSWER IS:

— SELECT id, person_id, validity_date FROM public.product WHERE validity_date IS NULL OR person_id IS NULL ORDER BY id DESC LIMIT 5 OFFSET 0;

— SELECT * FROM public.product WHERE id = ‘6336e118-1890-9600-0192-2b3cda14cd4a’;
— SELECT * FROM public.product WHERE id = ‘6329df6c-1890-9600-0157-61aa29e323bb’;

WITH rows AS (SELECT * FROM public.product WHERE validity_date IS NULL OR person_id IS NULL ORDER BY id DESC LIMIT 5 OFFSET 0)
UPDATE public.product main_rows
SET validity_date = to_timestamp(main_rows.clob::json->’validityDate’::text #>> ‘{}’, ‘yyyy-MM-dd’)::timestamp,
person_id = main_rows.clob::json->’personId’::text #>> ‘{}’
FROM rows
WHERE main_rows.id = rows.id
RETURNING main_rows.id, main_rows.person_id, main_rows.validity_date;

===========================================================================================================================================
SELECT * FROM public.product WHERE validity_date >= NOW() AND person_id = ‘f6ae122e-4d4d-45ab-a396-536d4bd8fb1c’ ORDER BY validity_date DESC;
SELECT clob::json->’personId’ as person_id FROM public.product WHERE validity_date >= NOW() AND person_id = ‘f6ae122e-4d4d-45ab-a396-536d4bd8fb1c’ ORDER BY validity_date DESC;