Can We get sum of list attributes in oracle without loop?

2
votes

can we get the sum of all hours in the below list? I am using the sum function with JSON_QUERY function but getting error.

 SET SERVEROUT ON
    
    DECLARE 
    data    varchar2(200);
    JSONSTRING  VARCHAR2(4000):= ' {"Implement_Details": [
        {
          "Hours": "100",
          "Implement1": "Laser Leveler"
        },
        {
          "Hours": "400",
          "Implement1": "Trolley"
        },
        {
          "Hours": "100",
          "Implement1": "Cultivator"
        }
      ]}';
      
      BEGIN 
      
      SELECT SUM(JSON_QUERY(JSONSTRING, '$.Implement_Details[*].Hours' WITH ARRAY WRAPPER)) INTO DATA
                FROM dual;
    
      
    DBMS_OUTPUT.PUT_LINE(data);
    
    end;

expected output: 100 + 400 + 100 = 600

Error report - ORA-01722: invalid number ORA-06512: at line 20 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

1
jsonoracleplsqloracle12c

1 Answers

2
votes

You cannot use SUM() on the whole ARRAY. What I did was I turned the Hours into a table and used the SUM() on the column, how you normally would in a SELECT clause.

SET SERVEROUT ON

DECLARE 
    data    varchar2(200);
    JSONSTRING  VARCHAR2(4000):= ' {"Implement_Details": [
        {
          "Hours": 100,
          "Implement1": "Laser Leveler"
        },
        {
          "Hours": 400,
          "Implement1": "Trolley"
        },
        {
          "Hours": 100,
          "Implement1": "Cultivator"
        }
      ]}';
      
      BEGIN 
      
        SELECT SUM(value) INTO DATA
          FROM JSON_TABLE((JSON_QUERY(JSONSTRING, '$.Implement_Details[*].Hours' WITH ARRAY WRAPPER)), '$[*]' COLUMNS (value PATH '$'));    
      
    DBMS_OUTPUT.PUT_LINE(data);
    
    end;