title: Query jsonb array on RAIL 5.2 postgres12
body: <p>on rails 5 with PostgreSQL 12.2, I have this model</p>
  <pre><code>  create_table &quot;training_opportunities&quot;, force: :cascade do |t|
      t.bigint &quot;contact_id&quot;
      t.string &quot;situation&quot;
      t.boolean &quot;cpf&quot;
      t.string &quot;software_interest&quot;
      t.string &quot;training_type_interest&quot;
      t.string &quot;month&quot;
      t.text &quot;comment&quot;
      t.datetime &quot;created_at&quot;, null: false
      t.datetime &quot;updated_at&quot;, null: false
      t.string &quot;status&quot;
      t.boolean &quot;pole_emploi&quot;, default: false
      t.jsonb &quot;history&quot;, default: [], array: true
      t.boolean &quot;to_be_call&quot;, default: false
      t.boolean &quot;is_flag&quot;, default: false
      t.boolean &quot;is_star&quot;, default: false
      t.index [&quot;contact_id&quot;], name: &quot;index_training_opportunities_on_contact_id&quot;
    end
  </code></pre>
  <p>I try to query TrainingOpportunity by the content of the history. For example, in rails c</p>
  <p><code>TrainingOpportunity.first.history</code></p>
  <p>give</p>
  <pre><code>[{&quot;date&quot;=&gt;&quot;2020-07-03 09:05:00 +0000&quot;, &quot;step&quot;=&gt;&quot;ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07&quot;}, {&quot;date&quot;=&gt;&quot;20/07/03&quot;, &quot;step&quot;=&gt;&quot;reprise automatique de l'existant&quot;}]
  </code></pre>
  <p>I try to find it whith this query:</p>
  <pre><code>TrainingOpportunity.where('history @&gt; {&quot;step&quot;:&quot;ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07&quot;}')
  </code></pre>
  <p>but I've got this error</p>
  <pre><code>ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near &quot;{&quot;)
  LINE 1: ...* FROM &quot;training_opportunities&quot; WHERE (history @&gt; {&quot;step&quot;:&quot;I...
                                                               ^
  : SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; {&quot;step&quot;:&quot;ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07&quot;}) LIMIT $1
  </code></pre>
  <p>I tried :</p>
  <pre><code>TrainingOpportunity.where('history @&gt; ?', '[{&quot;step&quot;:&quot;ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07&quot;}]')
  </code></pre>
  <p>but i got this error:</p>
  <pre><code>ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: &quot;[{&quot;step&quot;:&quot;ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07&quot;}]&quot;)
  LINE 1: ...* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '[{&quot;step&quot;:...
                                                               ^
  DETAIL:  &quot;[&quot; must introduce explicitly-specified array dimensions.
  : SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '[{&quot;step&quot;:&quot;ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07&quot;}]') LIMIT $1
  </code></pre>
  <h2>after rmlockerd  proposition , I tried the following solution</h2>
  <p>but no ones works.</p>
  <pre><code>TrainingOpportunity.where('history @&gt; ?', {&quot;date&quot;=&gt;&quot;20/07/03&quot;}.to_json)
    TrainingOpportunity Load (1.0ms)  SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '{&quot;date&quot;:&quot;20/07/03&quot;}') LIMIT $1  [[&quot;LIMIT&quot;, 11]]
  Traceback (most recent call last):
  ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: &quot;{&quot;date&quot;:&quot;20/07/03&quot;}&quot;)
  LINE 1: ...* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '{&quot;date&quot;:&quot;...
                                                               ^
  DETAIL:  Unexpected array element.
  : SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '{&quot;date&quot;:&quot;20/07/03&quot;}') LIMIT $1
  
  
  
  TrainingOpportunity.where('history @&gt; ?', {'date'=&gt;'20/07/03'}.to_json)
    TrainingOpportunity Load (1.0ms)  SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '{&quot;date&quot;:&quot;20/07/03&quot;}') LIMIT $1  [[&quot;LIMIT&quot;, 11]]
  Traceback (most recent call last):
  ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: &quot;{&quot;date&quot;:&quot;20/07/03&quot;}&quot;)
  LINE 1: ...* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '{&quot;date&quot;:&quot;...
                                                               ^
  DETAIL:  Unexpected array element.
  : SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '{&quot;date&quot;:&quot;20/07/03&quot;}') LIMIT $1
  
  
  
  TrainingOpportunity.where('history @&gt; ?', {date:'20/07/03'}.to_json)
    TrainingOpportunity Load (1.0ms)  SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '{&quot;date&quot;:&quot;20/07/03&quot;}') LIMIT $1  [[&quot;LIMIT&quot;, 11]]
  Traceback (most recent call last):
  ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: &quot;{&quot;date&quot;:&quot;20/07/03&quot;}&quot;)
  LINE 1: ...* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '{&quot;date&quot;:&quot;...
                                                               ^
  DETAIL:  Unexpected array element.
  : SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '{&quot;date&quot;:&quot;20/07/03&quot;}') LIMIT $1
  
  
  TrainingOpportunity.where('history @&gt; ?', [{date:'20/07/03'}].to_json)
    TrainingOpportunity Load (1.0ms)  SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '[{&quot;date&quot;:&quot;20/07/03&quot;}]') LIMIT $1  [[&quot;LIMIT&quot;, 11]]
  Traceback (most recent call last):
  ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: &quot;[{&quot;date&quot;:&quot;20/07/03&quot;}]&quot;)
  LINE 1: ...* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '[{&quot;date&quot;:...
                                                               ^
  DETAIL:  &quot;[&quot; must introduce explicitly-specified array dimensions.
  : SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history @&gt; '[{&quot;date&quot;:&quot;20/07/03&quot;}]') LIMIT $1
  </code></pre>
  <h2>I try to specify the first element of the array</h2>
  <pre><code>TrainingOpportunity.where('history --&gt; 0 = ?', '{&quot;date&quot;=&gt;&quot;20/07/03&quot;}')
    TrainingOpportunity Load (0.9ms)  SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history --&gt; 0 = '{&quot;date&quot;=&gt;&quot;20/07/03&quot;}') LIMIT $1  [[&quot;LIMIT&quot;, 11]]
  Traceback (most recent call last):
  ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at end of input)
  LINE 1: ...ies&quot; WHERE (history --&gt; 0 = '{&quot;date&quot;=&gt;&quot;20/07/03&quot;}') LIMIT $1
                                                                         ^
  : SELECT  &quot;training_opportunities&quot;.* FROM &quot;training_opportunities&quot; WHERE (history --&gt; 0 = '{&quot;date&quot;=&gt;&quot;20/07/03&quot;}') LIMIT $1
  </code></pre>