重要的提示:从 Postgres 10 及更高版本开始,情况发生了变化,因此请根据您的数据库版本选择正确的解决方案。发生了什么变化?从 Postgres 10 开始,不允许在 CASE 语句中使用集合返回函数,并且jsonb_array_elements
就是这样一个函数。
Postgres 10 之前的版本
在您的数据中必须有一些标量值而不是内部数组date
key.
您可以使用以下命令来识别特定键的类型jsonb_typeof()
然后将其包裹在一个CASE
陈述。
考虑下面的标量和数组示例作为输入集:
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
因此,您的查询需要像这样编写来处理这种情况:
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 希望我们为此编写明确的代码。
逻辑与上面相同(指10之前的pg版本),但我们将分两步而不是一步进行。
首先,我们需要找到两种类型的共同表示:数字和数组。我们可以用一个数字组成一个数组,因此数组将是一个不错的选择。我们所做的是为每种情况构建一个数组(阅读评论):
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
像这样的子句:
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