How to insert a jsonb[]

[lyb124553153]

Here is my code
I dont konw how to create a jsonb[] expression

[59] pry(main)> DB = Sequel.connect('postgres://postgres@localhost:5432/postgres')
[61] pry(main)> DB.extension :pg_array, :pg_json
[67] pry(main)> DB.create_table :foo do
[67] pry(main)*   column :bar, 'jsonb[]'
[67] pry(main)* end
=> nil
[68] pry(main)> json_array = Sequel.pg_jsonb([ {a: :b}])
=> [{:a=>:b}]
[69] pry(main)> DB[:foo].insert(bar: json_array)
Sequel::DatabaseError: PG::DatatypeMismatch: ERROR:  column "bar" is of type jsonb[] but expression is of type jsonb
LINE 1: INSERT INTO "foo" ("bar") VALUES ('[{"a":"b"}]'::jsonb) RETU...
                                          ^
HINT:  You will need to rewrite or cast the expression.

from /Users/liyongbo/.asdf/installs/ruby/2.6.3/lib/ruby/gems/2.6.0/gems/sequel-5.76.0/lib/sequel/adapters/postgres.rb:171:in `async_exec'
Caused by PG::DatatypeMismatch: ERROR:  column "bar" is of type jsonb[] but expression is of type jsonb
LINE 1: INSERT INTO "foo" ("bar") VALUES ('[{"a":"b"}]'::jsonb) RETU...
                                          ^
HINT:  You will need to rewrite or cast the expression

[tycooon]

Most likely you don't need jsonb[] type and you should use just jsonb. If you really need jsonb[], you should wrap your object with Sequel.pg_array method (see https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_array_rb.html)

[jeremyevans]

This is correct. The original example creates a JSON object which is an array, instead of PostgreSQL array of JSON objects. If you really want to create a jsonb[] : Sequel.pg_array(Sequel.pg_jsonb({a: :b}))

[lyb124553153]

Really appreciate for yout explanation, At last I figured out it should be Sequel.pg_array([Sequel.pg_jsonb({a: :b})])