Search code examples
rubyrom-rbrom-sql

How to query records with rom-sql by json column in postgres?

Having a table with jsonb column containing some array, what is best way to select records containing specific tag with rom-sql?

Example of query. https://www.db-fiddle.com/f/u4CFkUUpnHZj67j1RJ5YRe/0

CREATE TABLE posts (
  id INT,
  tags JSONB
);
INSERT INTO posts (id, tags) VALUES (1, '["cats", "dogs"]');
INSERT INTO posts (id, tags) VALUES (2, '["dogs"]');

SELECT * FROM posts WHERE tags @> '["cats"]';

So, how to build this query with rom-sql?

Solution

  • It's as simple as posts.where { tags.contain(['cats']) }. There are some problems with obtaining YARD for these methods because we're kind of too innovative. Anyhow, the whole list of currently supported methods/operators is on Github.