Answer a question

I want to check if a record already exist on database, but I have one json data type field and I need to compare it too. When I try check using exists? I got the following error:

SELECT  1 AS one FROM "arrangements"
WHERE "arrangements"."deleted_at" IS NULL AND "arrangements"."account_id" = 1
AND "arrangements"."receiver_id" = 19 AND "config"."hardware" = '---
category: mobile
serial: ''00000013''
vehicle: 
' AND "arrangements"."recorded" = 't' LIMIT 1

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "config"
LINE 1: ...id" = 1 AND "arrangements"."receiver_id" = 19 AND "config"."...
                                                         ^

Code that I using to check if a exists:

@arrangement = Arrangement.new({account_id: receiver.account.id, receiver_id: receiver.id, config: params[:config], recorded: true})

if Arrangement.exists?(account_id: @arrangement.account_id, receiver_id: @arrangement.receiver_id, config: @arrangement.config, recorded: @arrangement.recorded)
    puts 'true'
end

I already tried:

if Arrangement.exists?(@arrangement)
   puts 'true'
end

But always return false

Table:

create_table :arrangements do |t|
  t.references :account,    index:   true
  t.references :receiver,   index:   true
  t.json       :config,     null:    false
  t.boolean    :recorded,   default: false
  t.datetime   :deleted_at, index:   true
  t.integer    :created_by
  t.timestamps
end

Answers

You cannot compare jsons. Try to compare some jsons values

where("arrangements.config->>'category' = ?", params[:config][:category])

Look in postgresql docs for other JSON functions and operators

Logo

PostgreSQL社区为您提供最前沿的新闻资讯和知识内容

更多推荐