ArrayField maps to a real PostgreSQL array.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | # ====================================================================== # `ArrayField` maps to a real PostgreSQL array. The lookups become array # operators: `__contains` → `@>`, `__overlap` → `&&`, `__len` → `array_length`, # and `__0` indexes the first element. A GIN index makes containment/overlap # fast. # ====================================================================== # ---------------------------------------------------------------------- # tags contains ['fantasy'] # ---------------------------------------------------------------------- # Django: Book.objects.filter(tags__contains=["fantasy"]) # SQL: # SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper" # FROM "examples_book" # WHERE "examples_book"."tags" @> (ARRAY['fantasy'])::varchar(30)[] # Result: # Harry Potter # The Hobbit # The Lord of the Rings # ---------------------------------------------------------------------- # tags overlap ['tech','epic'] # ---------------------------------------------------------------------- # Django: Book.objects.filter(tags__overlap=["tech", "epic"]) # SQL: # SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper" # FROM "examples_book" # WHERE "examples_book"."tags" && (ARRAY['tech', 'epic'])::varchar(30)[] # Result: # The Lord of the Rings # Postgres for Authors # ---------------------------------------------------------------------- # exactly 3 tags (tags__len=3) # ---------------------------------------------------------------------- # Django: Book.objects.filter(tags__len=3) # SQL: # SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper" # FROM "examples_book" # WHERE CASE WHEN "examples_book"."tags" IS NULL THEN NULL ELSE coalesce(array_length("examples_book"."tags", 1), 0) END = 3 # Result: # The Lord of the Rings # ---------------------------------------------------------------------- # first tag = 'fantasy' (tags__0) # ---------------------------------------------------------------------- # Django: Book.objects.filter(tags__0="fantasy") # SQL: # SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper" # FROM "examples_book" # WHERE ("examples_book"."tags")[1] = 'fantasy' # Result: # Harry Potter # The Hobbit # The Lord of the Rings |
