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