Search in nested Postgresql JSONB column

Question

How to search in nested jsonb column in Rails.

Model: Shop
jsonb column: shop_data

shop_data: {
  common_data: {
    "image_url" => "https://sample.com/img.jpg"
    "token" => "AOsa2123ASDasdaasasda"
    "uid" => ""
    "expires_at" => ""
  }
}

Wanna make a scopes that will checks for records where:

1. shop_data->common_data->expires_at IS NOT NULL
2. shop_data->common_data->image_url IS NULL

Roman Alekseiev · Accepted Answer · 2020-02-20 09:02:59Z

0

 1. scope :expired, -> { where("shop_data -> 'common_data' ->> 'expires_at' IS NOT NULL") }
 2. scope :null_image, -> { where("shop_data -> 'common_data' ->> 'image_url' IS NULL") }

source JSON functions in postgres

Hope it helps

answered Feb 20, 2020 at 9:02

Roman Alekseiev

1,92120 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Search in nested Postgresql JSONB column

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