Django Fulltext search on JSON field

Question

I am trying to implement the newly added Fulltext-search Postgres support for Django 1.10.

One of the fields I'm trying to search on is a JSON field:

from django.contrib.postgres.fields import JSONField
class Product():
        attributes = JSONField(...)

If I try to do the following search using a SearchVector

Product.objects.annotate(
    search=SearchVector('attributes'),
).filter(search=keyword)

Will raise:

django.db.utils.DataError: invalid input syntax for type json
LINE 1: ...ol1, to_tsvector(COALESCE("product"."attributes", '')) AS "s...
                                                         ^
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1:

Which makes sense, a raw SQL query like this will need to be

select to_tsvector(attributes::text) from product;

But how can I achieve that field conversion inside Django syntax ?

Answer 1

As @e4c5 reported there's Cast function since Django 1.10 (the same version you used).

So if you would to search a JSON field as text you have to cast it as text:

from django.contrib.postgres.search import SearchVector
from django.db.models import TextField
from django.db.models.functions import Cast

Product.objects.annotate(
    search=SearchVector(Cast('attributes', TextField())),
).filter(search=keyword)

You can also use only specific part of your JSON field in your SearchVector:

from django.contrib.postgres.search import SearchVector
from django.contrib.postgres.fields.jsonb import KeyTextTransform

Product.objects.annotate(
    search=SearchVector(KeyTextTransform('key1', 'attributes')),
).filter(search=keyword)

PostgreSQL 10 added Full Text Search support for JSON and JSONB.