How to get aggregate SUM(price)
from "price"
in PostgreSQL select Following is my data schema and structure:
Table Name: modifiers
create_table "modifiers", force: :cascade do |t|
t.text "modifier_ids", default: [], array: true
t.text "modifier_data", default: [], array: true
t.integer "order_datum_id"
t.integer "inventory_count", default: 0
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.text "measure_ids", default: [], array: true
t.text "measure_data", default: [], array: true
end
Column name: modifiers_data
"{"{
\"modifier_id\"=>\"1327\",
\"price\"=>\"0.75\",
\"quantity\"=>\"1\",
\"name\"=>\"Cheese Crust\"
}","{
\"modifier_id\"=>\"1333\",
\"price\"=>\"0.5\",
\"quantity\"=>\"1\",
\"name\"=>\"Black Olives\"
}","{
\"modifier_id\"=>\"1347\",
\"price\"=>\"0.5\",
\"quantity\"=>\"1\",
\"name\"=>\"Chicken Tikka\"
}"}"
I want to get the sum of all values in array_json column. So, for example, for first row, I want:
id | price
---+-------
1 | 1.75
Where 1.75 = 0.75 + 0.5 + 0.5
->
and->>
operators ( postgresql.org/docs/9.3/static/functions-json.html ). Example:sum( modifiers_data->'price'::real )
– pumbo 12 hours ago