Thread: function 'IS JSON ARRAY' not identified
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@mac POSTGRES % psql
psql (14.18 (Homebrew), server 14.17 (Homebrew))
"Badia, Antonio" <antonio.badia@louisville.edu> writes:
> 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@mac POSTGRES % psql
> psql (14.18 (Homebrew), server 14.17 (Homebrew))
IS JSON ARRAY was added in PG v16.
regards, tom lane
Tom,
thanks for taking a look at this so quickly. Shame homebrew is so behind, but this is a kind of a relief.
Regards,
Antonio
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, June 2, 2025 11:19 AM
To: Badia, Antonio <antonio.badia@louisville.edu>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: function 'IS JSON ARRAY' not identified
Sent: Monday, June 2, 2025 11:19 AM
To: Badia, Antonio <antonio.badia@louisville.edu>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: function 'IS JSON ARRAY' not identified
CAUTION: This email originated from outside of our organization. Do not click links, open attachments, or respond unless you recognize the sender's email address and know the contents are safe.
"Badia, Antonio" <antonio.badia@louisville.edu> writes:
> 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@mac POSTGRES % psql
> psql (14.18 (Homebrew), server 14.17 (Homebrew))
IS JSON ARRAY was added in PG v16.
regards, tom lane
"Badia, Antonio" <antonio.badia@louisville.edu> writes:
> 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@mac POSTGRES % psql
> psql (14.18 (Homebrew), server 14.17 (Homebrew))
IS JSON ARRAY was added in PG v16.
regards, tom lane