Login

PostgreSQL JSON subqueries

Author:
dolamroth
Posted:
August 26, 2021
Language:
Python
Version:
3.0
Score:
1 (after 1 ratings)

Allows to fetch a row or array of rows of data, linked to parent object, in a single query. Data is fetched as JSON and is not serialized into Django objects.

Example:
from django.db import Models

class Book(models.Model):
    authors = models.ManyToMany('Author', through='BookToAuthor', blank=True)
    title = models.CharField(max_length=512, default='')

class Author(models.Model):
    name = models.CharField(max_length=512, default='')

class BookToAuthor(models.Model):
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    book = models.ForeignKey(Book, on_delete=models.CASCADE)
Download author with all his/her books in a single query
from django.db.models import OuterRef

books_by_author_subquery = Book.objects.filter(  
    id__in=BookToAuthor.objects.filter(author_id=OuterRef(OuterRef('id')))  
).values('title')

author = Author.objects\  
    .annotate(books=SubqueryJsonAgg(books_by_author_subquery))\  
    .get(id=1)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
from django.db.models import Subquery, JSONField

class PostgresqlJsonField(JSONField):
    def from_db_value(self, value, expression, connection):
        return value

class SubqueryJson(Subquery):
    """
    A replacement for select_related, that allows to fetch row of linked data as a subquery
    """
    template = "(SELECT row_to_json(_subquery) FROM (%(subquery)s) _subquery)"
    output_field = PostgresqlJsonField()

class SubqueryJsonAgg(Subquery):
    """
    A replacement for prefetch_related, that allows to fetch array of linked data as a subquery
    """
    template = "(SELECT array_to_json(coalesce(array_agg(row_to_json(_subquery)), array[]::json[])) FROM (%(subquery)s) _subquery)"
    output_field = PostgresqlJsonField()

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 11 months, 2 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 11 months, 3 weeks ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 6 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 7 months ago
  5. Help text hyperlinks by sa2812 1 year, 8 months ago

Comments

Please login first before commenting.