How to exclude parts of json, stored in Postgres, when selecting using ActiveRecord?

Question

This is a follow up question to this one:

How to select only part of json, stored in Postgres, with ActiveRecord

Say I have a model User, which has a field of type json called settings. Let's assume that this field looks roughly like this:

{
  color: 'red', 
  language: 'English', 
  subitems: 
    {
      item1: true, 
      item2: 43, 
      item3: ['foo', 'bar', 'baz']
    }
}

The difference to the question cited above is that I'd like to know how to exclude part of the json. So here, I want to select everything from settings except:

subitems: 
  {
    item1: true, 
    item2: 43, 
    item3: ['foo', 'bar', 'baz']
  }

Much more complex than the related question :] Would a solution using two queries suite you? First query to get all top level json keys, second query to actually select all fields expect for the ones you wish to exclude. — Dani
– Dani, Commented Feb 10, 2016 at 13:33
Well, actually, I wouldn't mind just selecting all the keys, that I need manually. So I would need something like: User.select("settings -> ['color', 'language'] AS modified_settings"), which should return objects, that respond to the method modified_settings, which should return {color: 'red', language: 'English'}. Do you know how to select multiple keys at once? — Alexander Popov
– Alexander Popov, Commented Feb 10, 2016 at 13:41
Unfortunately I could find a way to retrieve multiple keys at once either. But you can always construct you query dynamically with something like keys_to_fetch.map{ |k| "setting -> '#{k}' as setting_#{k}"}.join(',') — Dani
– Dani, Commented Feb 10, 2016 at 13:44

Nyein · Accepted Answer · 2016-11-28 08:35:16Z

2

in my case,
>>User.select("subitem -> 'item1' AS subitem1", "subitem->'item2' AS subitem2").map(&:attributes)

with rails 5

edited Nov 28, 2016 at 8:35

answered Nov 28, 2016 at 8:22

Nyein

2863 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

How to exclude parts of json, stored in Postgres, when selecting using ActiveRecord?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related