Select from database a specific JSON node using Rails/AREL?

Question

Pretend I have a table called books and it has a json type column called misc where some miscellaneous data has been saved. Using Rails/AREL I would like to extract just id and the number of pages (num_pages) from all the books.

In psql I can do the following:

SELECT id, misc->'num_pages' FROM books ;
  id   |   ?column?    
-------+---------------
 23562 | "220"
 22188 | "355"

(Note: the above query produces the same results with --> as with ->).

I expected this to work:

pry> Book.select("id, misc->>'num_pages'")
  Book Load(1.7ms)  SELECT  id, etc-->'num_pages' FROM "books"
  Book Load(1.5ms)  SELECT  id, etc-->'num_pages' FROM "books"
=> #<Book::ActiveRecord_Relation:0x3ff7e934d8f4>

pry> Book.select("id, misc->'num_pages'")
  Book Load(1.1ms)  SELECT  id, etc->'num_pages' FROM "books"
=> [#<Book:0x00007fefd3c11a68 id: 23562>, #<Book:0x00007fefd3c116a8 id: 22188>]

...when I remove the ->'num_pages' part, it returns the entire misc field (so I know it's almost working):

pry> Book.select("id, misc")
  Book Load(0.9ms)  SELECT  id, etc FROM "books"
=> [#<Book:0x00007fefe4e99fb8
  id: 23562,
  etc:
   {"num_pages"=>"220",
...

What did you get when you ran your two Rails ARELs? Error message? Nil? — Beartech
– Beartech, Commented Apr 21, 2020 at 21:32
Does my answer work? I'm not sure why pluck works over select. — Beartech
– Beartech, Commented Apr 21, 2020 at 21:53
It works - thank you. I added the deprecation warning I saw and the resolution that it conveniently suggests. I am still curious to see this solved with select. — user664833
– user664833, Commented Apr 21, 2020 at 21:56

user664833 · Accepted Answer · 2020-04-21 21:55:24Z

3

I can't get it done with .select but .pluck works in this form:

Book.pluck("id", "misc -> 'num_pages'")
  => [[23562, "220"], [22188,"355"]]

However, this will produce a deprecation warning:

DEPRECATION WARNING: Dangerous query method (method whose arguments are used 
as raw SQL) called with non-attribute argument(s): "misc->'num_pages'". 
Non-attribute arguments will be disallowed in Rails 6.0. This method should
not be called with user-provided values, such as request parameters or model
attributes. Known-safe values can be passed by wrapping them in Arel.sql().

So use Arel.sql() as suggested:

Book.pluck("id", Arel.sql("misc -> 'num_pages'"))
  => [[23562, "220"], [22188,"355"]]

edited Apr 21, 2020 at 21:55

user664833

19.7k20 gold badges97 silver badges151 bronze badges

answered Apr 21, 2020 at 21:39

Beartech

6,4812 gold badges20 silver badges43 bronze badges

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

2 Comments

Beartech Over a year ago

@user664833, What version are you using. I'm on Rails 5.1.7

user664833 Over a year ago

I'm on Rails 5.2.3.

Collectives™ on Stack Overflow

Select from database a specific JSON node using Rails/AREL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related