CODEWITHSUNDEEP
djangopostgresql
Phil Sheard
Phil Sheard

Reputation: 2162

Extract nested value from a Django jsonfield

Is there a way to query an object, 'extract' a nested piece of data from a JSONField field and then make it available as a custom, temporary field on each instance of the Queryset?

In my use case, I'm storing overflow metadata from Twitter's API in a data field for later use. I'd like to be able to access the nested field followers_count within TwitterPost.data.

I've read the docs about how to filter based on nested values but not how to extract it as a temporary field when generating a queryset.

Similarly, I've read the annotate docs for ways to create a custom temporary field but the examples all use aggregation functions on simple fields, so not JSONFields.

Thanks in advance for any suggestions.

Example model:

from django.contrib.postgres.fields import JSONField

class TwitterPost(models.Model):
    id = models.IntegerField()
    data = JSONField()

Example JSON value for the data field:

{
  'followers_count': 7172,
  "default_profile_image": false,
  "profile_text_color": "000000"
}

Pseudocode for what I'd like to be able to do:

TwitterPost.objects.annotate(followers_count=instance.data.followers_count)

Upvotes: 2

Views: 5520

Answers (2)

injaon
injaon

Reputation: 243

If you want to access the data inside a JSONField, you've to use __. In your example it will be something like this

TwitterPost.objects.annotate(followers_count=instance.data__followers_count)

Take a look to the documentation here

Upvotes: 1

Basalex
Basalex

Reputation: 1187

This is probably a late answer, but there is a way to do it

from django.contrib.postgres.fields.jsonb import KeyTransform

TwitterPost.objects.annotate(followers_count=KeyTransform('followers_count', 'data'))

OR KeyTextTransform could be used instead of KeyTransform (for converting to string)

Upvotes: 10

Related Questions