Thread

1. function 'IS JSON ARRAY' not identified

   Badia, Antonio <antonio.badia@louisville.edu> — 2025-06-02T14:41:34Z

   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))
   
   
   

2. Re: function 'IS JSON ARRAY' not identified

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-02T15:19:15Z

   "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
   
   
   
   

3. Re: function 'IS JSON ARRAY' not identified

   Badia, Antonio <antonio.badia@louisville.edu> — 2025-06-02T15:26:09Z

   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
   
   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