If you continue to read this article, I assume that you know Ruby, OOP in Ruby, RoR, and Active Record.
Yes, Postgresql support Array types to store. Based on their documentation:
PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.
Let's start our journey! (I use Rails API-only as example, but this article can be implemented in normal Rails as well)
Table of Contents:
1. Migration
2. Create
3. Show
4. Update
5. Query
6. Final Word
1. Migration
It is simple:
# db/migrate/*_create_books.rb
class CreateBooks < ActiveRecord::Migration[6.0]
def change
create_table :books do |t|
t.string :title
t.string :tags, array: true, default: []
t.integer :ratings, array: true, default: []
t.timestamps
end
add_index :books, :tags, using: 'gin'
add_index :books, :ratings, using: 'gin'
end
end
If you want to add new column:
# db/migrate/*_add_subjects_to_books.rb
class AddSubjectsToBooks < ActiveRecord::Migration
def change
add_column :books, :subjects, :string, array:true, default: []
end
end
Notice:
I define the column as t.string :tags, array: true not t.array :tags. Compare to jsonb, which t.jsonb :payload. This is because there is no "array" type in PostgreSQL, only "array of column type". PostgreSQL arrays aren't generic containers like Ruby arrays, they are more like arrays in C, C++, etc.
2. Create
Create a record is very simple too:
irb(main):001:0> Book.create(title: "Hacking Growth", tags: ["business", "startup"], ratings: [4, 5])
(0.1ms) BEGIN
Book Create (0.6ms) INSERT INTO "books" ("title", "tags", "ratings", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id" [["title", "Hacking Growth"], ["tags", "{business,startup}"], ["ratings", "{4,5}"], ["created_at", "2020-06-29 08:48:42.440895"], ["updated_at", "2020-06-29 08:48:42.440895"]]
(0.4ms) COMMIT
=> #<Book id: 1, title: "Hacking Growth", tags: ["business", "startup"], ratings: [4, 5], created_at: "2020-06-29 08:48:42", updated_at: "2020-06-29 08:48:42">
3. Show
Both tags and ratings now an array object:
irb(main):002:0> book = Book.first
Book Load (0.3ms) SELECT "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT $1 [["LIMIT", 1]]
irb(main):003:0> book.tags
=> ["business", "startup"]
irb(main):004:0> book.tags[0]
=> "business"
4. Update
To update, the most easiest way is:
irb(main):005:0> book.tags << 'management'
=> ["business", "startup", "management"]
irb(main):0006:0> book.save!
(0.1ms) BEGIN
Book Update (1.2ms) UPDATE "books" SET "tags" = $1, "updated_at" = $2 WHERE "books"."id" = $3 [["tags", "{business,startup,management}"], ["updated_at", "2020-06-29 08:54:36.731328"], ["id", 1]]
(0.4ms) COMMIT
=> true
irb(main):007:0> book.tags
=> ["business", "startup", "management"]
And any other way to add a value to an array object:
# This works
book.tags << 'management'
#This will work too
book.tags.push 'management'
# This is also will work
book.tags += ['management']
But do not do this: Book.first.tags << 'finance', it won't be saved to the database. Prove:
irb(main):008:0> Book.first.tags << "finance"
Book Load (0.3ms) SELECT "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT $1 [["LIMIT", 1]]
=> ["business", "startup", "management", "finance"]
irb(main):009:0> Book.first.save!
Book Load (0.3ms) SELECT "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT $1 [["LIMIT", 1]]
=> true
irb(main):010:0> Book.first.tags
Book Load (0.3ms) SELECT "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT $1 [["LIMIT", 1]]
=> ["business", "startup", "management"]
If you want to use raw SQL, you can check to the official documentation.
5. Query
Let say we want to search every single Book that have tags management:
# This is valid
irb(main):011:0> Book.where("'management' = ANY (tags)")
# This is more secure
irb(main):012:0> Book.where(":tags = ANY (tags)", tags: 'management')
# This is also valid
irb(main):013:0> Book.where("tags @> ?", "{management}")
What if we want to search every single book that DO NOT HAVE tags management:
irb(main):013:0> Book.where.not("tags @> ?", "{management}")
You can see the operators and their description in the official documentation.
Now, what if we want to search book that contain multiple tags, like management and startup:
# This is valid
irb(main):014:0> Book.where("tags @> ARRAY[?]::varchar[]", ["management", "startup"])
# This is valid
irb(main):015:0> Book.where("tags && ?", "{management,startup}")
# If you use where.not, you basically search for all that do not contain the parameter given.
Now what if we want to search all book that have rating more than 3:
irb(main):016:0> Book.where("array_length(ratings, 1) >= 3")
How about making our search a little bit more robust and supporting pattern matching:
# %gem% is manaGEMent
irb(main):017:0> Book.where("array_to_string(tags, '||') LIKE :tags", tags: "%gem%")
You can see all the operators and functions and their description in the official documentation.
6. Final Word
That's all from me. I'll update if I find something interesting.
source: myself and extract from many articles
Top comments (6)
here is my scope examples
example
For the multiple tags in the query I'd rather go with a simpler
join:It might not be fancy, but casting an empty array leads to a NULL and that might not be desirable 👀
This seems vulnerable to SQL injection with a payload like tags_array = ["'management'};", "malicious code here", "another fake tag query"]
Thanks for your article. How about when you have a form to create the record? What should be the syntax? I am struggling to make it. The form should submit an array element. Do you know how to do it? Thanks.
Thanks for the article, you did a great job.
I'm saving to use as quick reference.
this pairs well with citext type (tags typically are case insenstive)