1

I am quite a newbie to Cube.js. I have been trying to integrate Cube.js analytics functionality with my Ruby on Rails app. The database is PostgreSQL. In a database, there is a certain column called answers_json with jsonb data type which contains a nested hash. An example of data of that column is:

**answers_json:**
"question_weights_calc"=>
    {"314"=>{"329"=>1.5, "331"=>4.5, "332"=>1.5, "333"=>3.0},
     "315"=>{"334"=>1.5, "335"=>4.5, "336"=>1.5, "337"=>3.0},
     "316"=>{"338"=>1.5, "339"=>3.0}}

There are many more keys in the same column with the same hash structure as shown above. I posted the specific part because I would be dealing with this part only. I need assistance with accessing the values in the hash. The column has a nested hash. In the example above, the keys "314", "315" and "316" are Category IDs. The keys associated with Category ID "314" are "329","331","332", "333"; which are Question IDs. Each category will have multiple questions. For different records, the category and question IDs will be dynamic. For example, for another record, Category ID and Question IDs associated with that category id will be different. I need to access the values associated with the key question id. For example, to access the value "1.5" I need to do this in my schema file:

**sql: `(answers_json -> 'question_weights_calc' -> '314' ->> '329')`**

But the issue here is, those ids will be dynamic for different records in the database. Instead of "314" and "329", they can be some other numbers. Adding different record's json here for clarification:

**answers_json:**
"question_weights_calc"=>{"129"=>{"273"=>6.0, "275"=>15.0, "277"=>8.0}, "252"=>{"279"=>3.0, "281"=>8.0, "283"=>3.0}}}

How can I know and access those dynamic IDs and their values since I also need to perform mathematical operations on values. Thanks!

riddhi thummar
  • 21
  • 2
  • How do you determine the IDs you need to access in each structure? Will you get them externally? Or is it something computed based on the list of existing IDs in the structure? – Michal T Jan 05 '21 at 06:59
  • @MichalT When a user fills up a survey, this type of JSON record is created. i.e. The categories and questions are already created in the system database. When the user answers a particular question related to a specific category, we just link those IDs and the response by the user in the form of JSON. – riddhi thummar Jan 08 '21 at 03:06

1 Answers1

0

As a general rule, it's difficult to run SQL-based reporting on highly dynamic JSON data. Postgres does have some useful functions for dealing with JSON, and you might be able to use json_each or json_object_keys plus a few joins to get there, but its quite likely that the performance and maintainability of such a query would be difficult to say the least Cube.js ultimately executes SQL queries, so if you do go the above route, the query should be easily transferrable to a Cube.js schema.

Another approach would be to create a separate data processing pipeline that collects all the JSON data and flattens it into a single table. The pipeline should then store this data back in your database of choice, from where you could then use Cube.js to query it.

Hassan Khan
  • 766
  • 3
  • 9
  • 21