How to using json fields inside where statement in Sequel?

Question

I have a table transactions

 id             | bigint                      | NOT NULL DEFAULT nextval('transactions_id_seq'::regclass)
 transaction_id | bigint                      | NOT NULL
 middleware_id  | text                        | 
 opname         | optype                      | NOT NULL
 created_at     | timestamp without time zone | NOT NULL
 finished_at    | timestamp without time zone |
 request        | jsonb                       | NOT NULL
 answer         | jsonb                       |

Where request can contains data like: { plugin_id: string, item_src_id: string, item_dst_id: string, payload: {}} or { plugin_id: string, item_id: string, payload: {}}

I can select transaction list for some item_id like that with pure SQL:

SELECT id, request
  FROM transactions
  WHERE 'BEX456'
    IN (request->>'item_id', request->>'item_src_id', request->>'item_dst_id')

But, when I use that request with Sequel, it warns me about

SEQUEL DEPRECATION WARNING: Calling a dataset filtering method with multiple arguments or an array where the first argument/element is a string is deprecated and will be removed in Sequel 5.

For that request:

$db[:transactions].
  where("? IN (request->>'item_id', request->>'item_src_id', request->>'item_dst_id')", data[:item_id]).all

I can use Sequel.lit for that string inside the where op, but my question is - can I use native Sequel operators for selecting json inside field?

This works for me:

$db[:transactions].where(
  data[:item_id] => [
    Sequel.lit("request->>'item_id'"),
    Sequel.lit("request->>'item_dst_id'"),
    Sequel.lit("request->>'item_src_id'") ] ).first

D-side · Accepted Answer · 2017-07-20 10:27:01Z

6

Postgres' JSON operators are available in the pg_json_ops extension.

Load it beforehand:

Sequel.extension :pg_json_ops

And then, in your particular case:

# request ->> 'item_id'
:request.pg_jsonb.get_text('item_id') # WITH core extensions
Sequel.pg_jsonb_op(:request).get_text('item_id') # or WITHOUT

You have the power of Ruby too:

["item_id", "item_dst_id", "item_src_id"].map { |key| :request.pg_jsonb.get_text(key) }

edited Jul 20, 2017 at 10:27

answered Jul 20, 2017 at 10:20

D-side

9,5353 gold badges31 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

How to using json fields inside where statement in Sequel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related