current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite

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...], ...}

share|improve this question
    
Please provide your sample json value. The error seems pretty clear. Your value passed to jsonb_array_elements is a scalar, not a json array. – Kamil G. Aug 30 at 20:56
    
"stats_by_date": {"date": [16632, 16633, 16634, 16635, 16636, 16637, 16638, 16639, 16640, 16641, 16642, 16643, 16644, 16645, 16646, 16647, 16648, 16649, 16650, 16651, 16652, 16653, 16654, 16655, 16656, 16657, 16658, 16659, 16660, 16661, 16662, 16663, 16664, 16665, 16666, 16667, 16668, 16669, 16670, 16671, 16672, 16673, 16674], "imps": [2418, 896, 1005...], ...} – Hai Qu Aug 30 at 21:09
    
it works for limit to a number of entries, if loop on all data table, then the error comes out, so there might be some null/scalar passed to the jsonb_array_elements function. Want to know how to check and bypass it – Hai Qu Aug 30 at 21:15
up vote 0 down vote accepted

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
share|improve this answer
    
That works! Thanks a lot. – Hai Qu Aug 31 at 17:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.