Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

No authorized routine named "JSON_ARRAYAGG" of type "FUNCTION" having compatible arguments was found

No authorized routine named "JSON_ARRAYAGG" of type "FUNCTION" having compatible arguments was found Getting error while using JSON_ARRAYAGG function in DB2:

    QUERY:
        SELECT JSON_OBJECT('ACCT_ID' VALUE acct_id, 
        'use_case list' VALUE 
        JSON_ARRAYAGG(
        JSON_OBJECT('use_case' VALUE use_case,
        'stage' VALUE stage))) 
        FROM NULLID.ProductRecommendations 
        GROUP BY acct_id;
    Error:
            FAILED  [SELECT - 0 rows, 0.009 secs]  1) [Code: -440, SQL State: 42884]  No authorized routine named "JSON_ARRAYAGG" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.26.14
                2) [Code: -727, SQL State: 56098]  An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-440", SQLSTATE "42884" and message tokens "JSON_ARRAYAGG|FUNCTION".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.26.14  
I have tried this query on the sample tables, still getting the same error:
    Person Table:
    create table NULLID.person
      id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
      name        varchar(50),
      PRIMARY KEY (id)
    Contacts Table:
        create table NULLID.contacts
          id INT,
          person_id int,
          key   varchar(50),
          value varchar(100),
          foreign key (person_id) references person (id)
    Insert Queries:
    insert into NULLID.person values (1, 'A');
    insert into NULLID.person values (2, 'B');
    insert into NULLID.contacts values (1, 1, 'EMAIL', 'a@b.com');
    insert into NULLID.contacts values (2, 1, 'PHONE', '123');
    insert into NULLID.contacts values (3, 2, 'EMAIL', 'b@b.com');
    insert into NULLID.contacts values (4, 2, 'PHONE', '456');
    Query:  
SELECT
    person_id,
    JSON_ARRAYAGG(key)
    contacts
GROUP BY person_id
    Error:
16:17:44  FAILED  [SELECT - 0 rows, 0.009 secs]  1) [Code: -440, SQL State: 42884]  No authorized routine named "JSON_ARRAYAGG" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.26.14
2) [Code: -727, SQL State: 56098]  An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-440", SQLSTATE "42884" and message tokens "JSON_ARRAYAGG|FUNCTION".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.26.14  
                Which Db2 server platform and version hosts your database?  ( is the Db2-server running on Linux/Unix/Windows/CLOUD ,   as/400 (i-series),  or  mainframe Z/OS) ?
– mao
                Oct 21, 2020 at 9:36
                The Db2-server is running on On CLOUD. I have tried running it on local docker container with the latest image of DB2, still getting the same error. I am using windows 10 with the latest version of Docker & DB2 image.
– pranitaT
                Oct 21, 2020 at 10:07
                And from where are your examples that Db2 supports JSON_ARRAYAGG? AFAIK Db2 has JSON_ARRAY and ARRAY_AGG, but nothing combined
– data_henrik
                Oct 21, 2020 at 10:52
Please understand that you must use correct documentation to match the Db2 product platform. For Db2 there are three main platforms (Linux/Unix/Windows(LUW) ,  Z/OS,  and  i-series (as/400).  Additionally the Db2-on-cloud has slightly different features (and a different knowledge centre) than the regular on-premises Db2-LUW.
You quoted a page for Db2-for-i (as/400) that is a different product (with different SQL syntax) than Db2-on-cloud.  So Db2-on-cloud does not have that function json_arrayagg (although Db2 for i series at current versions does have that function).
Please use the correct Knowledge Centre for Db2-on-cloud which is here
https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.kc.doc/welcome.html
With Db2 on cloud the syntax is to use functions JSON_ARRAY and related functions.  You can use this query to see which JSON functions are available:
select routinename from syscat.routines where routinename like 'JSON%'
(assuming you have the correct access).
                I want to aggregate the data using an ID with a nested array containing multiple JSON objects
– pranitaT
                Oct 21, 2020 at 11:45
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.