Unable to retrieve data from Postgres json column

Question

I have a Postgres table with json column as dataset_metadata. It stores an epoc time in array looks like:

select 
 event_type
,technology
,dataset_metadata->> 'intervals_epoch_seconds'
,add_ts from fact_completeness_count_requests;

When I run above, I get this:

 event_type | technology | ?column? | add_ts
------------------+------------+----------------------------------------------------------------------------------------------------------+----------------------------
 INGESTED_DATASET | USPP | [[1720707300, 1720708200], [1720708200, 1720709100], [1720709100, 1720710000], [1720706400, 1720707300]] | 2024年08月08日 04:28:13.3942
 INGESTED_DATASET | PLTE | [[1720287900, 1720288800]] | 2024年08月08日 04:29:24.786225
 INGESTED_DATASET | LTE | [[1720711800, 1720712700]]

I want that it should return the min and max epoc time from a given row and convert it into date and display it in separate column:

select to_timestamp(1720711800);
pm-cm-state=> \d fact_completeness_count_requests
 Table "public.fact_completeness_count_requests"
 Column | Type | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+---------
 event_type | character varying(255) | | not null |
 technology | character varying(255) | | |
 dataset_metadata | json | | not null |

We are using PostgreSQL 9.6.

• Erwin Brandstetter: Please provide your table definition and sample data as SQL commands, so we can easily recreate your case: CREATE TABLE, INSERT. And always disclose your Postgres version.

Answer 1

You want to unnest multiple JSON array dimensions (before converting).
A simple and safe solution is trickier than it might seem.
For a maximum of two array dimensions:

SELECT *
FROM tbl t
CROSS JOIN LATERAL (
 SELECT to_timestamp(min((a.ts #>> '{}')::float)) AS min_ts
 , to_timestamp(max((a.ts #>> '{}')::float)) AS max_ts
 FROM jsonb_path_query(t.data->'intervals_epoch_seconds', '$[*][*]') a(ts)
 WHERE t.data->'intervals_epoch_seconds' IS JSON ARRAY -- may or may not be necessary; requires pg 16+
-- WHERE jsonb_typeof(t.data->'intervals_epoch_seconds') = 'array' -- fallback for pg 15-
 ) j;

fiddle

jsonb_path_query requires Postgres 12 or later.
... IS JSON ARRAY` requires Postgres 16 or later. (I provided a fallback.)

See:

• jsonb_array_elements() fails with "ERROR: cannot extract elements from an object"
• Find rows containing a key in a JSONB array of records

To note, a normalized relational design instead of the nested JSON array would be simpler and faster.

• Vikrant Singh Rana: We are using PostgreSQL 9.6 version and getting error message when running below query
• Vikrant Singh Rana: SELECT * FROM fact_completeness_count_requests t CROSS JOIN LATERAL ( SELECT to_timestamp(min((a.ts #>> '{}')::float)) AS min_ts , to_timestamp(max((a.ts #>> '{}')::float)) AS max_ts FROM jsonb_path_query(t.dataset_metadata->'intervals_epoch_seconds', '$[][]') a(ts) WHERE jsonb_typeof(t.dataset_metadata->>'intervals_epoch_seconds') = 'array' ) j; ERROR: function jsonb_path_query(json, unknown) does not exist LINE 6: FROM jsonb_path_query(t.dataset_metadata->'intervals_ep... ^
• Erwin Brandstetter: Postgres 9.6 is hopelessly outdated - unsupported since 2021. See: postgresql.org/support/versioning I added version info. Please always declare your version of Postgres in your question right away.