Extract json array from postgres table gives error: cannot extract elements from a scalar

Question

By using jsonb_array_elements() function to extract out jsonb data array from Postgres, it gave error:

cannot extract elements from a scalar

I assume that it is because of the NULL in the return call, added the NULL checking condition but not work. Any help 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:

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

Answer 1

IMPORTANT NOTE: Things changed from Postgres 10 and up, so head to the right solution according to your database version. What changed? Set returning functions are disallowed from use in CASE statements from Postgres 10 onwards, and jsonb_array_elements is such a function.

Postgres version before 10

In your data there must be some scalar value instead of an array inside date key.

You can identify of which type is a particular key with jsonb_typeof() and then wrap it up inside a CASE statement.

Consider below example of scalar and array as your input 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

So your query needs to be written like this 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

Postgres version 10+

Since set returning functions are disallowed from Pg10, we need to write a bit more code to achieve the same. Set returning function means that function call can output more than one row and is disallowed from being used in a CASE statement. Simply put, Postgres wants us to write explicit code for this.

Logic stays the same as above (refering to pg version before 10), but we will be doing it in two-steps instead of one.

First, we need to find common representation for both types: number and array. We can make an array out of one number, so an array would be a good choice. What we do is build an array for every case (read comments):

  case when jsonb_typeof(jsonb_column->'stats_by_date'->'date') = 'array' -- if array
       then jsonb_column->'stats_by_date'->'date' -- leave it as it is
       else jsonb_build_array(jsonb_column->'stats_by_date'->'date') -- if not array, build array
  end as date

Second step would be to wrap our data type transformation within one statement using WITH clause and then select from it with the use of function call in the FROM clause like this:

with json_arrays as (
select 
  case when jsonb_typeof(jsonb_column->'stats_by_date'->'date') = 'array' 
       then jsonb_column->'stats_by_date'->'date'
       else jsonb_build_array(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)
)
select t.date
from 
  json_arrays j -- this is refering to our named WITH clause
, jsonb_array_elements(date) t(date) -- call function to get array elements

Answer 2

After some research I see there was a change at PostgreSQL 10 that broke the original answer.

Here is how I did the example in 10.

select jsonb_array_elements(test.date) as date
from
(select
  case when jsonb_typeof(jsonb_column->'stats_by_date'->'date') = 'array'
       then jsonb_column->'stats_by_date'->'date'
       else jsonb_build_array(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)) as test;