JSON object: Query a value from unkown node based on a condition-CodePudding

I'm trying to query two values (DISCOUNT_TOTAL and ITEM_TOTAL) from a JSON object in a PostgreSQL database. Take the following query as reference:

SELECT
    mt.customer_order
    totals -> 0 -> 'amount' -> centAmount DISCOUNT_TOTAL
    totals -> 1 -> 'amount' -> centAmount ITEM_TOTAL
FROM
    my_table mt
    to_jsonb(my_table.my_json -> 'data' -> 'order' -> 'totals') totals
WHERE
    mt.customer_order in ('1000001', '1000002')

The query code works just fine, the big problem is that, for some reason out of my control, the values DISCOUNT_TOTAL and ITEM_TOTAL some times change their positions in the JSON object from one customer_order to other:

JSON Object

So i cannot aim to totals -> 0 -> 'amount' -> centAmount assuming that it contains the value related to type : DISCOUNT_TOTAL (same for type: ITEM_TOTAL). Is there any work around to get the correct centAmount for each type?

CodePudding user response：

Use a path query instead of hardcoding the array positions:

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;

db<>fiddle here

EDIT: In case your PostgreSQL version does not support json path queries, you can do it by expanding the array into rows and then doing a pivot by case and sum:

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;

db<>fiddle here