Retrieving json array from postgres table gives error: cannot retrieve elements from scalar

Question

Using the jsonb_array_elements() function to retrieve the jsonb from Postgres, it gave an error:

cannot retrieve elements from a scalar

I assume that due to NULL check condition has been added in the callback, but it does not work. Any help was appreciated.

  select id , CASE WHEN report IS NULL OR (report->'stats_by_date') IS NULL OR (report->'stats_by_date'-> 'date') IS NULL then to_json(0)::jsonb ELSE jsonb_array_elements(report -> 'stats_by_date' -> 'date') END AS Date from factor_reports_table 

The truncated json array looks like this:

"stats_by_date": {"date": [16632, 16633, 16634, ...], "imps": [2418, 896, 1005 ...], ...}

Answer 1

Your data should have some scalar value instead of an array inside the date key.

You can determine what type is a specific key with jsonb_typeof() , and then wrap it inside a CASE statement.

Consider the example of a scalar and an array as your input data set:

 select case when jsonb_typeof(jsonb_column->'stats_by_date'->'date') = 'array' then jsonb_array_elements(jsonb_column->'stats_by_date'->'date') else jsonb_column->'stats_by_date'->'date' end as date from ( select '{"stats_by_date": {"date": 123}}'::jsonb -- scalar (type: 'number') union all select '{"stats_by_date": {"date": [456]}}'::jsonb -- array (type: 'array') ) foo(jsonb_column); 

Result

  date ------ 123 456 

Thus, your request should be written in such a way as to handle such cases:

 select id, case when jsonb_typeof(jsonb_column->'stats_by_date'->'date') = 'array' then jsonb_array_elements(jsonb_column->'stats_by_date'->'date') else jsonb_column->'stats_by_date'->'date' end as date from factor_reports_table