Rails PostgreSQL jsonb GROUP BY query on internal dynamic keys

Question

I have an ActiveRecord model let's call it API::Response and within that we have column named error_messages which stores the error messages when the API call fails.

The structure of API::Response is something like:

#<API::Response:0x0000000019579298
 id: "fffefdae43c0",
 api_id: "11c91374f10e",
 parent_id: nil,
 code: "ABC123",
 meta_field: "{}",
 lock_version: 1,
 created_at: Thu, 01 Feb 2019 15:28:37 UTC +00:00,
 updated_at: Tue, 09 Oct 2019 20:32:03 UTC +00:00,
 version: 1538352000,
 error_messages: {"api_failure_1"=> "API failure reason 1", "api_failure_2"=> "API failure reason 2"},
 output_data: { "foo" => "bar"},
 override_applied: false>

What I want to do is to get a result where I am grouping by keys within the error_messages column and get the count of occurrence each key:

so some something like:

key                   | count
-----------------------------
api_failure_1         |  1
api_failure_2         |  3
some_other_failure    |  n
...

where keys in the error_messages jsonb field are dynamic.

I tried to do something like

API::Response.where.not("error_messages = '{}'").group("error_messages").count

but this just gives me count of each distinct error_messages

=> {
 {"api_failure_1"=> "API failure 1",
  "api_failure_2"=> "API failure 2"}=>1,
 {"api_failure_1"=> "API failure 1",
  "api_failure_3"=> "API failure 3"}=>1
}

rather then the count of each key within.

I am on Rails 5.2 and PostgreSQL 10.4

Any pointers or help is very much appreciated.

bo-oz · Accepted Answer · 2019-05-14 07:09:40Z

4

As a matter of fact... you are able to make it work! Do the following query:

query = <<-SQL 
  select errors.types, COUNT(errors.types)
  from (select jsonb_object_keys(error_messages) as types from api_responses) errors
  group by errors.types
SQL

your_result = API::Response.find_by_sql(query)

I'd still advise to create a View, that makes accessing it a lot easier.

answered May 14, 2019 at 7:09

bo-oz

2,8822 gold badges31 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bo-oz · Accepted Answer · 2019-05-14 06:03:41Z

0

I don't think PostgreSQL is able to do groupings on keys in a jsonb field. The approach I think you need to do: query all responses with errors, then use a loop on all found records to group with normal Ruby code.

# not tested, but something like:
errors = API::Response.where.not("error_messages = '{}'").pluck(:error_messages)
error_holder = []
errors.each do |error|
  error_holder << error.keys
end
counts = Hash.new(0)
error_holder.flatten.each { |name| counts[name] += 1 }
puts counts

answered May 14, 2019 at 6:03

bo-oz

2,8822 gold badges31 silver badges50 bronze badges

2 Comments

harshs08 Over a year ago

I was thinking of some similar but curious if we can do something on db level directly without loading the data in memory since the table is pretty big.

bo-oz Over a year ago

I create a new answer

Collectives™ on Stack Overflow

Rails PostgreSQL jsonb GROUP BY query on internal dynamic keys

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related