Join the Stack Overflow Community
Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

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

share|improve this question
    
You can access JSON values with -> and ->> operators ( postgresql.org/docs/9.3/static/functions-json.html ). Example: sum( modifiers_data->'price'::real ) – pumbo 12 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.