Retrieve value from JSONField

[StefanBrand]

I'd like to retrieve values from a JSONField because all contents of the whole JSON bloat the vector tiles. Doesn't seem to be possible. Do you see a way?

I tried this (adapted from test suite):

class Feature(models.Model):
    geom = models.GeometryField(srid=4326)
    name = models.CharField(max_length=250)
    layer = models.ForeignKey(Layer, on_delete=models.CASCADE, related_name='features')
    date = models.DateField(null=True)
    json = models.JSONField(null=True)

        Feature.objects.create(
            name="feat1",
            geom="POINT(0 0)",
            layer=cls.layer,
            date="2020-07-07",
            json={"foo": "bar", "baz": "oof"}
        )

1st Attempt (related field notation)

class PostGISFeatureWithJSONView(PostgisMVTView, ListView):
    queryset = Feature.objects.all()
    vector_tile_layer_name = "features"
    vector_tile_fields = ('json__foo', )

results in (Nothing included in MVT)

-                             'properties': {},
+                             'properties': {'foo': 'bar'},

2nd Attempt (annotate related field notation)

class PostGISFeatureWithJSONView(PostgisMVTView, ListView):
    queryset = Feature.objects.annotate(foo=F('json__foo'))
    vector_tile_layer_name = "features"
    vector_tile_fields = ('foo', )

results in (whole JSON included in MVT)

-                             'properties': {'baz': 'oof', 'foo': 'bar'},
?                                            --------------
+                             'properties': {'foo': 'bar'},

3rd Attempt (KeyTransform)

from django.db.models.fields.json import KeyTransform

class PostGISFeatureWithJSONView(PostgisMVTView, ListView):
    queryset = Feature.objects.annotate(foo=KeyTransform("foo", "json"))
    vector_tile_layer_name = "features"
    vector_tile_fields = ('foo', )

results in (Nothing contained in MVT)

-                             'properties': {},
+                             'properties': {'foo': 'bar'},

[submarcos]

3rd attempt should be right. KeyTextTransform seems to be working. I don't know why KeyTextTransform is included and KeyTransform not. first is '->>' operator, second is '->'. No differences in final query. Maybe ST_ASMVT doesn't like simple '->'

[submarcos]

OK, I understand. https://www.postgresql.org/docs/9.3/functions-json.html

According documentation, "->" operator return JSONB single value. Django can decode it as SQL values, but it seems that ST_ASMVT doesn't.

For text or nontyped values, you should use KeyTextTransform.

Feature.objects.annotate(foo=KeyTextTransform('foo', 'json'))

For other, you should cast :

Feature.objects.annotate(foo=Cast(KeyTextTransform('foo', 'json'), output_field=IntegerField()))

[StefanBrand]

You are a genius! Works splendidly!

I'm closing as this is a ST_ASMVT limitation.

[submarcos]

Il will add this workaround in documentation