从postgres表中提取json数组给出错误:无法从标量中提取元素 [英] Extract json array from postgres table gives error: cannot extract elements from a scalar

问题描述

通过使用jsonb_array_elements()函数从Postgres中提取jsonb数据数组，它给出了错误:

By using jsonb_array_elements() function to extract out jsonb data array from Postgres, it gave error:

无法从标量中提取元素

cannot extract elements from a scalar

我认为是由于返回调用中的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:

"stats_by_date":{"date":[16632，16633，16634，...]，"imps":[2418，896，1005 ...]，...}

"stats_by_date": {"date": [16632, 16633, 16634, ...], "imps": [2418, 896, 1005...], ...}

推荐答案

重要说明:从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数组给出错误:无法从标量中提取元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！