Activerecord Query postgres jsonb array for integer member

Question

I have the following table Profile with a jsonb column

item_type	object_changes
"Item"	[{"customer_id": [1, 5], "other_id": 1}, {"customer_id": [4, 5], "other_id": 2}]
"Item"	[{"customer_id": [3, 6], "other_id": 3}, {"customer_id": [3, 5], "other_id": 2}]

I want to be able to query using active record to find all rows that has customer_id 5.

I tried doing the following but it doesn't work

Profile.where("object_changes->'customer_id' @> '5'")

Profile.where("object_changes->'customer_id' @> ?::jsonb", [5].to_json)

Profile.where("? = ANY (object_changes->>'customer_id')", 5)

Does anyone know how i can be able to make this query in Ruby on Rails.

My Rails version is Rails 4.2 and Ruby version is 2.4.10 and I am using postgres as my DB

Konstantin Strukov · Accepted Answer · 2021-08-16 21:52:59Z

1

I think what you need is a combination of jsonb_to_recordset and lateral join.

For the following schema and data

CREATE TABLE profiles (
  id integer,
  item_type text,
  object_changes jsonb
);

INSERT INTO profiles(id, item_type, object_changes) VALUES
  (1, 'Item', '[{"customer_id": [1, 5], "other_id": 1}, {"customer_id": [4, 5], "other_id": 2}]'::jsonb),
  (2, 'Item', '[{"customer_id": [3, 6], "other_id": 3}, {"customer_id": [3, 5], "other_id": 2}]'::jsonb),
  (3, 'Item', '[{"customer_id": [4, 7], "other_id": 3}, {"customer_id": [8, 9], "other_id": 2}]'::jsonb);

Something like this would work:

SELECT distinct profiles.*
FROM 
  profiles, 
  jsonb_to_recordset(profiles.object_changes) AS changes(customer_id integer[], other_id integer)
WHERE 5 = ANY(changes.customer_id);


 id | item_type |                                  object_changes
----+-----------+----------------------------------------------------------------------------------
  2 | Item      | [{"other_id": 3, "customer_id": [3, 6]}, {"other_id": 2, "customer_id": [3, 5]}]
  1 | Item      | [{"other_id": 1, "customer_id": [1, 5]}, {"other_id": 2, "customer_id": [4, 5]}]
(2 rows)

So the final solution with AR query interface is something like (I hardcode the value to find but I believe you get the idea and parametrization is not a problem):

Profile.find_by_sql(<<~SQL)
  SELECT distinct profiles.*
  FROM 
    profiles, 
    jsonb_to_recordset(profiles.object_changes) AS changes(customer_id integer[], other_id integer)
  WHERE 5 = ANY(changes.customer_id)
SQL

edited Aug 16, 2021 at 21:52

answered Jul 21, 2021 at 9:57

Konstantin Strukov

3,0591 gold badge12 silver badges14 bronze badges

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

1 Comment

Kingsley Simon Over a year ago

i end up getting an error PG::InvalidTextRepresentation: ERROR: malformed array literal: for the customer_id integer[]

Collectives™ on Stack Overflow

Activerecord Query postgres jsonb array for integer member

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related