Sql error 22023 error cannot extract elements from a scalar

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.

Postgres version before 10

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

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.

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

ReportServer Forum

You are not logged in.

#1 2021-02-10 08:56:49

Retrieve a jsonb object from Postgresql

This 2021, I have been reviewing for internal use Reportserver 3.3.0 with Postgresql datasource, reports are fine. That is until I came across tables that contain JSON.

How do I extract JSON objects for reports? The TYPE was recognised as VARCHAR and not JSON

Example of a JSON data, how do I extract only the value for «name»?

I have tried this SQL QUERY but got the error as below;

SELECT p.product -> ‘name’ FROM products AS p

Query could not be executed: ERROR: operator does not exist: character varying -> unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 224

Last edited by nicholas (2021-02-10 09:03:30)

#2 2021-02-10 09:32:12

Re: Retrieve a jsonb object from Postgresql

is this a valid postgres query (sending it directly, without ReportServer)? SELECT p.product -> ‘name’ FROM products AS p
can you pls post a screenshot of the results?

and if you put this query into a dynamic list, what do you see exactly? can you pls post the complete error stack ?

#3 2021-02-10 11:07:53

Re: Retrieve a jsonb object from Postgresql

This is specific for Postgresql, and I have prepared the sample as below

Data structure:
CREATE TABLE public.products (
id int4 NOT NULL PRIMARY KEY,
«name» varchar NOT NULL,
product jsonb NULL
);

The direct SQL query

SELECT name, jsonb_agg(t->’name’) as brand, jsonb_agg(t->’value’) as code FROM products, jsonb_array_elements(products.product) t
group by name ;

The results
name |brand |code |
————|————————|——————-|
Apple Pie |[«Apple Pie»] |[«1All-1-1000-xzp»]|
Orange bod |[«Orange Gala»] |[«1Bll-1-99-aaa»] |
Chicken Pie|[«Downtown Chicken Pie»]|[«1Bll-1-201-aaa»] |

When run sql in Reportserver

Error
Query could not be executed: ERROR: function jsonb_array_elements(character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 653

Is there an alternative method to make that query? One problem I face, not knowing the supported Query functions for JSON in Reportserver.

Last edited by nicholas (2021-02-10 11:25:47)

Источник

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Subject: Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar Date: 2018-03-02 16:21:44 Message-ID: 0b0c6908-1b0d-0f3f-4dd3-d6000326b02c@aklaver.com Views: Raw Message | Whole Thread | Download mbox | Resend email Thread: Lists: pgsql-general

On 03/02/2018 06:42 AM, Alexander Farber wrote:
> Hi Adrian, I 100% agree that nobody except me should debug my huge
> stored function, but if you look at my PostgreSQL 10.3 log —
>

Which proves what has already been proven, that at least some of the
data is correct. The issue is data that is not correct as evidenced by
the error message:

select mid, jsonb_array_length(tiles) from words_moves where gid=609;
ERROR: 22023: cannot get array length of a scalar
LOCATION: jsonb_array_length, jsonfuncs.c:1579

This is not going to get solved until you identify the ‘bad’ tiles data.

Источник

ОШИБКА: невозможно вызвать json_array_elements для не-массива

У меня есть запрос Postgres, который возвращает следующие значения. Этот запрос возвращает одни данные для идентификатора.

Теперь я хочу получить бизнес-адрес для всего населения в таблице dw_data_job . Я изменил запрос на следующий.

Теперь я получаю ошибку следующего содержания:

Тип столбца dw_data_job.addresses — text , и данные выглядят следующим образом — когда значением JSON является массив :

Другое примерное значение с не-массивом (объектом):

Запрос должен содержать данные json как в виде массива, так и без него, чтобы получить адрес, город, штат и код страны для всех строк данных, в которых вложенный ключ #text имеет значение ‘BUSINESS’ .

1 ответ

Как сообщает вам сообщение об ошибке (и указал Джефф), в таблице dw_data_job есть одна или несколько строк, где столбец addresses не содержит допустимого массива JSON (или NULL ) . Допустимый литерал JSON, да, иначе мы увидели бы другое сообщение об ошибке, вызванное неудачным преобразованием в ::json , но не массив JSON .

Выполните этот запрос, чтобы определить строки с нарушением:

(Включает значения NULL в addresses , что не приведет к возникновению сообщаемой ошибки, но также может быть проблемой.)

Чтобы просто пропустить строки с недопустимыми данными и продолжить запрос:

Я упростил синтаксис с помощью компактного оператора #> для глубоко вложенных значений и возвращать все text вместо некоторых json (как обоснованное предположение).

Чтобы включить как простые объекты, так и массивы, вы можете:

Источник

On 03/02/2018 06:42 AM, Alexander Farber wrote:
> Hi Adrian, I 100% agree that nobody except me should debug my huge
> stored function, but if you look at my PostgreSQL 10.3 log —
>

Which proves what has already been proven, that at least some of the
data is correct. The issue is data that is not correct as evidenced by
the error message:

select mid, jsonb_array_length(tiles) from words_moves where gid=609;
ERROR: 22023: cannot get array length of a scalar
LOCATION: jsonb_array_length, jsonfuncs.c:1579

This is not going to get solved until you identify the ‘bad’ tiles data.

> 2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.110 ms  parse
> <unnamed>: SELECT out_uid  AS uid,  out_fcm  AS fcm,  out_apns AS apns,
> out_adm  AS adm,  out_body AS body  FROM words_play_game($1::int,
> $2::int, $3::jsonb)
> 2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms  bind
> <unnamed>: SELECT out_uid  AS uid, out_fcm  AS fcm,  out_apns AS apns,
> out_adm  AS adm,  out_body AS body FROM words_play_game($1::int,
> $2::int, $3::jsonb)
> 2018-03-02 15:30:33.646 CET [16693] DETAIL:  parameters: $1 = ‘7’, $2 =
> ‘609’, $3 =
> ‘[{«col»:0,»letter»:»К»,»row»:3,»value»:2},{«col»:0,»letter»:»И»,»row»:4,»value»:1}]’
> 2018-03-02 15:30:33.646 CET [16693] LOG:  execute <unnamed>: SELECT
> out_uid AS uid,  out_fcm  AS fcm,  out_apns AS apns, out_adm  AS adm,
> out_body AS body  FROM words_play_game($1::int, $2::int, $3::jsonb)
> 2018-03-02 15:30:33.646 CET [16693] DETAIL:  parameters: $1 = ‘7’, $2 =
> ‘609’, $3 =
> ‘[{«col»:0,»letter»:»К»,»row»:3,»value»:2},{«col»:0,»letter»:»И»,»row»:4,»value»:1}]’
>
> I just pass as the 3rd argument in_tiles to my stored function:
> ‘[{«col»:0,»letter»:»К»,»row»:3,»value»:2},{«col»:0,»letter»:»И»,»row»:4,»value»:1}]’
>
> and then take the in_tiles and store it unchanged in the words_moves table:
>
>         INSERT INTO words_moves (
>                 action,
>                 gid,
>                 uid,
>                 played,
>                 tiles
>         ) VALUES (
>                 ‘play’,
>                 in_gid,
>                 in_uid,
>                 CURRENT_TIMESTAMP,
>                 in_tiles
>         ) RETURNING mid INTO STRICT _mid;
>
> Does anybody happen to see what could I do wrong there?
>
> Thank you for any hints
> Alex
>
> P.S: Here my stored fuinction:
> https://gist.github.com/afarber/88a832a1b90a8940764ad69b2b761914
>         Here my table:
> https://gist.github.com/afarber/06cc37114ff8dd14f05077f312904361
>         And here is how I call the stored function from Java:
>
>    String SQL_PLAY_GAME            =
>             «SELECT » +
>                 «out_uid  AS uid,  » +
>                 «out_fcm  AS fcm,  » +
>                 «out_apns AS apns, » +
>                 «out_adm  AS adm,  » +
>                 «out_body AS body  » +
>             «FROM words_play_game(?::int, ?::int, ?::jsonb)»;
>
>     private void handlePlay(int gid, String tiles) throws SQLException,
> IOException {
>         LOG.info(«handlePlay: {} -> {} {}», mUid, gid, tiles);
>         try (Connection db = DriverManager.getConnection(DATABASE_URL,
> DATABASE_USER, DATABASE_PASS);
>                 PreparedStatement st =
> db.prepareStatement(SQL_PLAY_GAME)) {
>             st.setInt(1, mUid);
>             st.setInt(2, gid);
>             st.setString(3, tiles);
>             runPlayerAction(st, gid);
>         }
>     }

—
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com