function 'IS JSON ARRAY' not identified

From: "Badia, Antonio" <antonio(dot)badia(at)louisville(dot)edu>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: function 'IS JSON ARRAY' not identified
Date: 2025-06-02 14:41:34
Message-ID: BN7PR03MB347651B5C4951F037352DBA18B62A@BN7PR03MB3476.namprd03.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Table 'Jsondata2' contains 2 columns, 'id' and 'data'. 'data' is of jsonb type.
When running query
select d.data - 'keya' || jsonb_build_object('keya', jsonb_array_elements(d.data::jsonb-> 'keya'))
from "Jsondata2" as d
where jsonb_exists(d.data '$.keya') and d.data->'keya' is not null and d.data->'keya' IS JSON ARRAY;
I get the error message:
ERROR: syntax error at or near "JSON" LINE 3: ... d.data->'keya' is not null and d.data->'keya' IS JSON ARRAY... ^
SQL state: 42601
Character: 213

Running Postgresql on MacOS (Sequoia 15.5), installed via homebrew:
antonio(at)mac POSTGRES % psql
psql (14.18 (Homebrew), server 14.17 (Homebrew))

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2025-06-02 15:19:15 Re: function 'IS JSON ARRAY' not identified
Previous Message Matheus Alcantara 2025-06-02 14:05:12 Re: BUG #18920: LOAD '$libdir/plugins' no longer works in 18beta1