问题描述
通过使用jsonb_array_elements()
函数从Postgres中提取jsonb
数据数组,它给出了错误:
By using jsonb_array_elements()
function to extract out jsonb
data array from Postgres, it gave error:
我认为是由于返回调用中的NULL
,所以添加了NULL
检查条件,但不起作用.任何帮助表示赞赏.
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
截断的json数组看起来像:
The truncated json array looks like:
推荐答案
重要说明:从Postgres 10起,事情发生了变化,因此请根据您的数据库版本使用正确的解决方案.发生了什么变化?从Postgres 10开始,在CASE语句中不允许使用set返回函数,而jsonb_array_elements
就是这样的函数.
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.
您的数据中必须有一些标量值,而不是date
键中的数组.
In your data there must be some scalar value instead of an array inside date
key.
您可以使用jsonb_typeof()
识别哪种类型是特定键,然后将其包装在CASE
语句中.
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);
结果
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版本10 +
由于Pg10禁止使用返回函数集,因此我们需要编写更多代码才能实现相同功能.设置返回函数意味着函数调用可以输出多行,并且不允许在CASE语句中使用. 简单地说,Postgres希望我们为此编写明确的代码.
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.
逻辑与上面的(指10之前的pg版本)相同,但是我们将分两步而不是一步来完成.
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
第二步是使用WITH
子句将我们的数据类型转换包装在一条语句中,然后使用FROM
子句中的函数调用从中进行选择,如下所示:
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
这篇关于从postgres表中提取json数组给出错误:无法从标量中提取元素的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!