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

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

Posted Aug 20, 2023
By Borislav Dostumski
2 min read

ANSWER IS:

  • UPDATE public.product SET validity_date = to_timestamp(clob::json->’validityDate’::text #» ‘{}’, ‘yyyy-MM-dd’)::timestamp WHERE id = ‘6331f937-1890-9600-01fd-eafd88efa78f’;
  • UPDATE public.product SET validity_date = to_timestamp(clob::json->’validityDate’::text #» ‘{}’, ‘yyyy-MM-dd’)::timestamp WHERE person_id = ‘d887e1b2-189e-4b49-a740-9c4c251ef68b’;
  • UPDATE public.product SET person_id = clob::json->’personId’::text #» ‘{}’ WHERE id = ‘6331f937-1890-9600-01fd-eafd88efa78f’;
  • 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;
  1. 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);
1
2
3
4
5
6
7

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;
  1. 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);
1
2
3

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;
1
2
3

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

1
2
3
4

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’;
1
2
3
4
5
6
7

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;
Issues
Issues