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

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"
      },
share|improve this question

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.