Sum values of JSON array in oracle sql-CodePudding

I have a Table which contains data like id, country, city,capacity. Capacity is a JSON array field which has values like [{'totalCapacity':100.0},{'totalCapacity':90.0},{'totalCapacity':80.0},{'totalCapacity':80.0}] against a row in table, so row in table looks like

id	country	city	capacity
1	usa	new-york	[{'totalCapacity':100.0},{'totalCapacity':90.0},{'totalCapacity':80.0},{'totalCapacity':80.0}]

Now I want result as

id	country	city	sum(capacity)
1	usa	new-york	350

i.e sum of totalCapacity against each row.

Tried this

SELECT
    id,
    country,
    city,
    SUM(JSON_VALUE(capacity, '$.totalCapacity')) AS total_capacity
FROM
    A 
GROUP BY
    id,
    country,
    city;

but getting nulls in total_capacity

CodePudding user response：

You can CROSS JOIN LATERAL a JSON_TABLE and aggregate in that:

SELECT t.id,
       t.country,
       t.city,
       j.totalCapacity
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT SUM(totalCapacity) AS totalCapacity
         FROM   JSON_TABLE(
                  t.capacity,
                  '$[*]'
                  COLUMNS (
                    totalcapacity NUMBER PATH '$.totalCapacity'
                  )
                )
       ) j

Which, for the sample data:

CREATE TABLE table_name (
  id       NUMBER,
  country  VARCHAR2(50),
  city     VARCHAR2(50),
  capacity CLOB CHECK (capacity IS JSON)
);

INSERT INTO table_name (
  id,
  country,
  city,
  capacity
) VALUES (
  1,
  'usa',
  'new-york',
  '[{"totalCapacity":100.0},{"totalCapacity":90.0},{"totalCapacity":80.0},{"totalCapacity":80.0}]'
);

Outputs:

ID	COUNTRY	CITY	TOTALCAPACITY
1	usa	new-york	350

fiddle

CodePudding user response：

From 21, you can also use sum() item function directly in json_value:

with data(js) as (
    select '[{"totalCapacity":100.0},{"totalCapacity":90.0},{"totalCapacity":80.0},{"totalCapacity":80.0}]' from dual
)
select json_value(js, '$[*].totalCapacity.sum()')
as val
from data
;

val
350

CodePudding user response：


SELECT
    id,
    country,
    city,
    SUM(j.totalCapacity) AS total_capacity
FROM
    A
CROSS APPLY OPENJSON(capacity) WITH (totalCapacity FLOAT '$.totalCapacity') AS j
GROUP BY
    id,
    country,
    city;