Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


with sample (jdata) as (
values (
'{
	"data": {
		"order": {
			"email": "something",
			"totals": [
			    {
			    	"type": "ITEM_TOTAL",
			    	"amount": {
			    		"centAmount": 14990
			    	}
			    },
			    {
			    	"type": "DISCOUNT_TOTAL",
			    	"amount": {
			    		"centAmount": 6660
			    	}
			    }
			]
		}
	}
}'::jsonb)
)
select jsonb_path_query_first(
          jdata, 
         '$.data.order.totals[*] ? (@.type == "DISCOUNT_TOTAL").amount.centAmount'
       ) as discount_total,
       jsonb_path_query_first(
          jdata, 
         '$.data.order.totals[*] ? (@.type == "ITEM_TOTAL").amount.centAmount'
       ) as item_total
  from sample;


discount_total
item_total


6660
14990


with sample (order_id, jdata) as (
values ( 1, 
'{
	"data": {
		"order": {
			"email": "something",
			"totals": [
			    {
			    	"type": "ITEM_TOTAL",
			    	"amount": {
			    		"centAmount": 14990
			    	}
			    },
			    {
			    	"type": "DISCOUNT_TOTAL",
			    	"amount": {
			    		"centAmount": 6660
			    	}
			    }
			]
		}
	}
}'::jsonb)
)
select order_id, 
       sum(
         case
           when el->>'type' = 'DISCOUNT_TOTAL' then (el->'amount'->'centAmount')::int
           else 0
         end 
       ) as discount_total,
       sum(
         case
           when el->>'type' = 'ITEM_TOTAL' then (el->'amount'->'centAmount')::int
           else 0
         end 
       ) as item_total
  from sample
       cross join lateral jsonb_array_elements(jdata->'data'->'order'->'totals') as a(el)
 group by order_id;


order_id
discount_total
item_total


1
6660
14990