postgresql 从 postgres 表中提取 json 数组给出错误：无法从标量中提取元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39236893/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extract json array from postgres table gives error: cannot extract elements from a scalar
提问by Hai Qu
By using jsonb_array_elements()function to extract out jsonbdata array from Postgres, it gave error:
通过使用jsonb_array_elements()函数jsonb从 Postgres 中提取数据数组，它给出了错误：
cannot extract elements from a scalar
无法从标量中提取元素
I assume that it is because of the NULLin the return call, added the NULLchecking condition but not work. Any help appreciated.
我认为这是因为NULL在返回调用中，添加了NULL检查条件但不起作用。任何帮助表示赞赏。
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:
截断的 json 数组如下所示：
"stats_by_date": {"date": [16632, 16633, 16634, ...], "imps": [2418, 896, 1005...], ...}
"stats_by_date": {"date": [16632, 16633, 16634, ...], "imps": [2418, 896, 1005 ...], ...}
回答by Kamil Gosciminski
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_elementsis such a function.
重要说明：从 Postgres 10 及更高版本开始，事情发生了变化，因此请根据您的数据库版本选择正确的解决方案。发生了什么变化？从 Postgres 10 开始，不允许在 CASE 语句中使用集合返回函数，它jsonb_array_elements就是这样一个函数。
Postgres version before 10
Postgres 10 之前的版本
In your data there must be some scalar value instead of an array inside datekey.
在您的数据中，必须有一些标量值而不是date键内的数组。
You can identify of which type is a particular key with jsonb_typeof()and then wrap it up inside a CASEstatement.
您可以识别特定键属于哪种类型jsonb_typeof()，然后将其包含在CASE语句中。
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+
Postgres 版本 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.
由于 Pg10 不允许设置返回函数，因此我们需要编写更多代码来实现相同的功能。设置返回函数是指函数调用可以输出多行，不能在CASE 语句中使用。简而言之，Postgres 希望我们为此编写明确的代码。
Logic stays the same as above (refering to pg version before 10), but we will be doing it in two-steps instead of one.
逻辑和上面一样（参考pg 10之前的版本），但我们将分两步完成，而不是一步。
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 WITHclause and then select from it with the use of function call in the FROMclause like this:
第二步是将我们的数据类型转换包装在一个 usingWITH子句中，然后使用子句中的函数调用从中进行选择，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
回答by TurboGus
After some research I see there was a change at PostgreSQL 10 that broke the original answer.
经过一些研究，我发现 PostgreSQL 10 有一个改变打破了原来的答案。
Here is how I did the example in 10.
以下是我在 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;
