I'm having some difficulty constructing an active record query on a jsonb object in postgresql. I'm trying to construct a query that will return the values of all of the keys called "amount". Basically, I want to figure out the average account balance for the account over a period of time. The object returned by the api is deeply nested, and the table with the jsonb object belongs to the users table. I haven't been able to find any good examples of a query like this.
schema.rb
create_table "users", force: :cascade do |t|
t.string "email"
t.string "password"
t.string "auth_token"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.string "yodlee_username"
t.string "yodlee_password"
end
create_table "yodlee_data", force: :cascade do |t|
t.jsonb "data", default: {}, null: false
t.integer "user_id"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
//sample data return and saved in table
"yodlee_datum": {
"id": 1,
"data": {
"numberOfHits": 102,
"searchResult": {
"transactions": [
{
"memo": {},
"price": {
"amount": 0,
"currencyCode": "USD"
},
"amount": {
"amount": 75.75,
"currencyCode": "USD"
},
"status": {
"statusId": 2,
"description": "pending",
"origStatusId": 0,
"localizedDescription": "pending"
},
"account": {
"siteName": "Wells Fargo - Bank",
"sumInfoId": 5,
"accountName": "EVERYDAY CHECKING",
"accountNumber": "xxxx8421",
"isAccountName": 1,
"itemAccountId": 10199595,
"accountBalance": {
"amount": 1751.68,
"currencyCode": "USD"
},
"decryptionStatus": true,
"accountDisplayName": {
"defaultNormalAccountName": "Wells Fargo - Bank - EVERYDAY CHECKING"
},
"itemAccountStatusId": 1
},
"viewKey": {
"rowNumber": 1,
"containerType": "bank",
"isParentMatch": false,
"transactionId": 15617992,
"transactionCount": 102,
"isSystemGeneratedSplit": false,
"recurringTransactionId": 0
},
"category": {
"categoryId": 14,
"isBusiness": false,
"categoryName": "Insurance",
"categoryTypeId": 3,
"categoryLevelId": 3,
"localizedCategoryName": "Insurance"
},
"postDate": "2016-03-01T00:00:00-0800",
"isMedical": false,
"isTaxable": false,
"isBusiness": false,
"isPersonal": false,
"checkNumber": {},
"description": {
"viewPref": false,
"description": "PURCHASE GEICO *AUTO xxx xxx 3000 DC CARD9305",
"merchantName": "GEICO",
"simpleDescription": "GEICO",
"transactionTypeDesc": "Insurance payment",
"isOlbUserDescription": false
},
"isClosingTxn": 0,
"isReimbursable": false,
"runningBalance": 0,
"transactionDate": "2016-03-01T00:00:00-0800",
"transactionType": "debit",
"transactionTypeId": 2,
"accessLevelRequired": 1,
"classUpdationSource": "S",
"transactionBaseType": "debit",
"categorizationKeyword": "geico",
"transactionBaseTypeId": 2,
"categorisationSourceId": 23,
"transactionPostingOrder": 0,
"localizedTransactionType": "debit",
"investmentTransactionView": {
"netCost": 0,
"holdingType": {
"holdingTypeId": 0
},
"lotHandling": {
"lotHandlingId": 0
}
},
"transactionSearchResultType": "aggregated",
"localizedTransactionBaseType": "debit"
},