Query jsonb array on RAIL 5.2 postgres12

Question

on rails 5 with PostgreSQL 12.2, I have this model

  create_table "training_opportunities", force: :cascade do |t|
    t.bigint "contact_id"
    t.string "situation"
    t.boolean "cpf"
    t.string "software_interest"
    t.string "training_type_interest"
    t.string "month"
    t.text "comment"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "status"
    t.boolean "pole_emploi", default: false
    t.jsonb "history", default: [], array: true
    t.boolean "to_be_call", default: false
    t.boolean "is_flag", default: false
    t.boolean "is_star", default: false
    t.index ["contact_id"], name: "index_training_opportunities_on_contact_id"
  end

I try to query TrainingOpportunity by the content of the history. For example, in rails c

TrainingOpportunity.first.history

give

[{"date"=>"2020-07-03 09:05:00 +0000", "step"=>"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}, {"date"=>"20/07/03", "step"=>"reprise automatique de l'existant"}]

I try to find it whith this query:

TrainingOpportunity.where('history @> {"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}')

but I've got this error

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "{")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> {"step":"I...
                                                             ^
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> {"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}) LIMIT $1

I tried :

TrainingOpportunity.where('history @> ?', '[{"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}]')

but i got this error:

ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "[{"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}]")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '[{"step":...
                                                             ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '[{"step":"ID POLE EMPLOI : 1242050E - architecture intérieur - débutante qlq notions de 3D  - environ 2500€CPF - devis envoyé le 03/07"}]') LIMIT $1

after rmlockerd proposition , I tried the following solution

but no ones works.

TrainingOpportunity.where('history @> ?', {"date"=>"20/07/03"}.to_json)
  TrainingOpportunity Load (1.0ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "{"date":"20/07/03"}")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '{"date":"...
                                                             ^
DETAIL:  Unexpected array element.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1



TrainingOpportunity.where('history @> ?', {'date'=>'20/07/03'}.to_json)
  TrainingOpportunity Load (1.0ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "{"date":"20/07/03"}")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '{"date":"...
                                                             ^
DETAIL:  Unexpected array element.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1



TrainingOpportunity.where('history @> ?', {date:'20/07/03'}.to_json)
  TrainingOpportunity Load (1.0ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "{"date":"20/07/03"}")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '{"date":"...
                                                             ^
DETAIL:  Unexpected array element.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '{"date":"20/07/03"}') LIMIT $1


TrainingOpportunity.where('history @> ?', [{date:'20/07/03'}].to_json)
  TrainingOpportunity Load (1.0ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '[{"date":"20/07/03"}]') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  malformed array literal: "[{"date":"20/07/03"}]")
LINE 1: ...* FROM "training_opportunities" WHERE (history @> '[{"date":...
                                                             ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history @> '[{"date":"20/07/03"}]') LIMIT $1

I try to specify the first element of the array

TrainingOpportunity.where('history --> 0 = ?', '{"date"=>"20/07/03"}')
  TrainingOpportunity Load (0.9ms)  SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history --> 0 = '{"date"=>"20/07/03"}') LIMIT $1  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at end of input)
LINE 1: ...ies" WHERE (history --> 0 = '{"date"=>"20/07/03"}') LIMIT $1
                                                                       ^
: SELECT  "training_opportunities".* FROM "training_opportunities" WHERE (history --> 0 = '{"date"=>"20/07/03"}') LIMIT $1

rmlockerd · Accepted Answer · 2020-07-04 21:57:17Z

1

EDIT

My original answer (and your initial attempts) assumed a column declared with the Postgres jsonb datatype. However, you have also created it as a Postgres Array (i.e., jsonb[] rather than jsonb). Postgres supports the @> operator for both arrays and jsonb types, and there's some sort of interaction between the two that I'm frankly not equipped to help you untangle. The good news is for what you seem to be wanting I don't think it's necessary. You have a couple of options:

If you will be doing lots of querying of this data, consider normalising it into a History model and just use a has_many relationship on Training Opportunity. In addition to making querying easier, you get all the other goodness of a model like validations, etc.
If you really want to keep this as JSON, I suggest to recreate the history column as just a jsonb type (without the array: true). You can still treat it as an array in practice (since arrays are valid JSON), and the @> operator will work as I outline below.

TrainingOpportunity.where('history @> ?', [{step:"reprise automatique de l'existant"}].to_json)
 => #<ActiveRecord::Relation [#<TrainingOpportunity id: 153, history: [..., {"date"=>"20/07/03", "step"=>"reprise automatique de l'existant"}], ...>

TrainingOpportunity.where('history @> ?', [{date: "20/07/03"}].to_json)
=> #<ActiveRecord::Relation [#<TrainingOpportunity id: 153, history: [..., {"date"=>"20/07/03", "step"=>"reprise automatique de l'existant"}], ...>

edited Jul 4, 2020 at 21:57

answered Jul 4, 2020 at 5:53

rmlockerd

4,1462 gold badges21 silver badges28 bronze badges

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

5 Comments

Denis Bolomier Over a year ago

thanks for your answer but it doesn't work, I don't know why ? I have edit my initial post

rmlockerd Over a year ago

I loaded your sample data into a model in my environment and those queries worked for me. I'll have a look.

Denis Bolomier Over a year ago

thanks mlockerd, i'm on mac os or heroku , on postgres 12.2 , rails 5.2

Denis Bolomier Over a year ago

may be this operator could help ? #>>

rmlockerd Over a year ago

Unfortunately, no. The JSON operators like #>> work on jsonb columns, but yours is set up as a jsonb[] column, which means Postgres treats it primarily as an array, so only the Array operators work on it. If what you want is to use the JSON operators to query your data directly, my answer stands; migrate the column to eliminate the array part. It's adding complexity but no value to you.

Collectives™ on Stack Overflow

Query jsonb array on RAIL 5.2 postgres12

after rmlockerd proposition , I tried the following solution

I try to specify the first element of the array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

after rmlockerd proposition , I tried the following solution

I try to specify the first element of the array

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related