Sample Header Ad - 728x90

unable to extract json column data (posgres 9.6)

0 votes
2 answers
264 views
I can run below query to get the data in given format as: SELECT to_timestamp(unnest(ARRAY[[1725042600, 1725043500], [1725041700, 1725042600], [1725043500, 1725044400], [1725040800, 1725041700]])); to_timestamp ------------------------ 2024-08-30 18:30:00+00 2024-08-30 18:45:00+00 2024-08-30 18:15:00+00 2024-08-30 18:30:00+00 2024-08-30 18:45:00+00 2024-08-30 19:00:00+00 2024-08-30 18:00:00+00 2024-08-30 18:15:00+00 (8 rows) I am using postgres version 9.6 and below is schema test_database=> \d fact_completeness_count_requests Table "public.fact_completeness_count_requests" Column | Type | Collation | Nullable | Default -------------------------+-----------------------------+-----------+----------+--------- request_id | character varying(64) | | not null | event_type | character varying(255) | | not null | technology | character varying(255) | | | vendor | character varying(255) | | | name | character varying(255) | | not null | dataset_metadata | json | | not null | I am using below query to genrate the expected output. As shown in query output above. Not sure how to use array and to_timestamp function with below query. Is it possible to archive with postgres 9.6 version select request_id ,dataset_metadata->> 'intervals_epoch_seconds' as epoc_seconds from fact_completeness_count_requests ; 31319ad1-e848-4ec3-9c3e-967981e2ae45-0 | [[1725048000, 1725051600]] 7a05cc38-5303-417d-88ce-fe3a604570d2 | [[1725055200, 1725056100]] ae6c2b09-8a95-4ac0-9846-6e76071579af | [[1725050700, 1725051600], [1725049800, 1725050700], [1725048900, 1725049800], [1725048000, 1725048900]]
Asked by vikrant rana (291 rep)
Aug 31, 2024, 12:30 PM
Last activity: Sep 3, 2024, 02:22 PM