Question 277582 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Sum values of JSON array in oracle sql

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search