How can I structure my JSON response using the Django Rest Framework serializer?

Question

I have a database table:

servicenumber | meternumber | usagedatetime | usage
11111         | 22222       | 2019-01-01    | 1.85
11111         | 22222       | 2019-01-02    | 2.25
11111         | 22222       | 2019-01-03    | 1.55
11111         | 22222       | 2019-01-04    | 2.15
11111         | 33333       | 2019-02-01    | 2.95
11111         | 33333       | 2019-02-02    | 3.95
11111         | 33333       | 2019-02-03    | 2.05
11111         | 33333       | 2019-02-04    | 3.22

As you can see, a service number can be related to multiple meter numbers. Think of the service number as a unique identifier for a geographic location that doesn't change.

And I have a Django model:

class MeterUsage(models.Model):

    objectid = models.IntegerField(
        db_column='OBJECTID', unique=True, primary_key=True)

    servicenumber = models.IntegerField(
        db_column='serviceNumber', blank=True, null=True)

    meternumber = models.IntegerField(
        db_column='meterNumber', blank=True, null=True)

    usagedatetime = models.DateTimeField(
        db_column='usageDateTime', blank=True, null=True)

    usage = models.DecimalField(
        max_digits=38, decimal_places=8, blank=True, null=True)

And I have a basic serializer:

class MeterUsageSerializer(serializers.ModelSerializer):

    class Meta:
        model = MeterUsage
        fields = (
            'usagedatetime',
            'usage'
        )

And the current response is:

[
    {
        "usagedatetime": "2019-01-01",
        "usage": "1.85"
    },
    {
        "usagedatetime": "2019-01-02",
        "usage": "2.25"
    },
    {
        "usagedatetime": "2019-01-03",
        "usage": "1.55"
    },

    ....
]

But what I really want is (need the usage separated by meter number):

[
  {
    "servicenumber": "11111",
    "meternumber": "22222",
    "usagedata": [
      {
        "usagedatetime": "2019-01-01",
        "usage": "1.85"
      },
      {
        "usagedatetime": "2019-01-02",
        "usage": "2.25"
      },
      {
        "usagedatetime": "2019-01-03",
        "usage": "1.55"
      },
      {
        "usagedatetime": "2019-01-04",
        "usage": "2.15"
      },

      ...
    ]
  },
  {
    "servicenumber": "11111",
    "meternumber": "33333",
    "usagedata": [
      {
        "usagedatetime": "2019-02-01",
        "usage": "2.95"
      },
      {
        "usagedatetime": "2019-02-02",
        "usage": "3.95"
      },
      {
        "usagedatetime": "2019-02-03",
        "usage": "2.05"
      },
      {
        "usagedatetime": "2019-02-04",
        "usage": "3.22"
      },

      ...
    ]
  },

  ...
]

My end goal is to display this data in a line chart using the ChartJS library in an Angular 7 app, and the data needs to be in this format:

  chartData = [
    {
      label: '22222',
      data: [1.85, 2.25, 1.55, 2.15] 
    },
    { 
      label: '33333',
      data: [2.95, 3.95, 2.05, 3.22]
    }
  ];

Is it possible to use a serializer to format the data as I've shown above? I've tried different techniques based on various tutorials I've read, but nothing seems to work and now I'm just confused about how to handle it.

Any insight is appreciated!

Answer 1

If you are not planning to change your model structure, you can not do what you want with a ModelSerializer. With ModelSerializer, you get 1 item in the resulting list for each database row, but you want to merge several rows into a single item in the resulting list. What you can try however, is selecting unique servicenumner - meternumber pairs and directly subclass Serializer to serializer your data.

In your view:

queryset = MeterUsage.objects.values('servicenumber', 'meternumber').distinct()
return Response(MeterUsageSerializer(queryset, many=True).data)

And your seriliazer:

class MeterUsageSerializer(serializers.Serializer):
    servicenumber = serializers.IntegerField()
    meternumber = serializers.IntegerField()
    usagedata = serializer.SerializerMethodField()

    def get_usagedata(self, obj):
        return [{
            'usagedatetime': item.usagedatetime.strftime('%Y-%m-%d'),
            'usage': item.usage
        } for item in MeterUsage.objects.filter(servicenumber=obj['servicenumber'], meternumber=obj['meternumber'])]

If you can change your model structure, you can structure your models and serializers in the following way:

class MeterUsage(models.Model):

    objectid = models.IntegerField(
        db_column='OBJECTID', unique=True, primary_key=True)

    servicenumber = models.IntegerField(
        db_column='serviceNumber', blank=True, null=True)

    meternumber = models.IntegerField(
        db_column='meterNumber', blank=True, null=True)

    class Meta:
        unique_together = ('servicenumber', 'meternumber')


class MeterUsageData(models.Model):

    meterusage = models.ForeignKey(MeterUsage, on_delete=models.CASCADE, related_name='data')

    usagedatetime = models.DateTimeField(
        db_column='usageDateTime', blank=True, null=True)

    usage = models.DecimalField(
        max_digits=38, decimal_places=8, blank=True, null=True)

class MeterUsageDataSerializer(serializers.ModelSerializer):
    usagedatetime = serializers.DateTimeField(format=''%Y-%m-%d'')

    class Meta:
        model = MeterUsageData
        fields = ('usagedatetime', 'usage')

class MeterUsageSerializer(serializers.ModelSerializer):
    data = MeterUsageDataSerializer(many=True)

    class Meta:
        model = MeterUsage
        fields = ('servicenumber', 'meternumber', 'data')

With this model - serializers structure, you can get the output like this in the view:

queryset = MeterUsage.objects.filter(...)
return Response(MeterUsageSerializer(queryset, many=True).data)

Note that with both these approaches, you issue a database query for each servicenumber - meternumber pair.

Answer 2

This is just very simple task. Let me give you an insight..

The easy way is to create two tables.

The First (main) table should hold informations for service number and Meter number. The second table should be a sub table that will hold informations for usage and datetime.

Since the service number as a unique id, you can create the table to have a foreign key reference. Anyway the issue of foreign key is optional as you can get everything working without it.

In your code you will need to loop through the main table and then get the sub table where service number in the main table matches that of the sub table. Thats all.

Think of this as Post and comment tables will generate that kind of arrays with Post being the main table and Comment being a sub.

Let me know what you feel..

Answer 3

I don't know exactly how to do this, but maybe something kind of like this:

# assuming you have current_response set to your first data format described in your question

# add "header dict" to every entry in current_response list, save as output

output = [dict() for x in len(current_response)]

for i, entry in current_response:
    output[i] = {
        "servicenumber": "11111",
        "meternumber": i,
        "usagedata": current_response[i],
    }

# create chartData list with dict for every data point containing both meternumber and usagedata

chartData = []

for entry in output:
    data_point = {
        label: entry["meternumber"],
        data: entry["usagedata"]["usage"]
    },
    chartData.append(data_point)

print(chartData)

Be warned, I haven't tested this, so I don't think it's working, but maybe I help you a bit at least with the concept of doing this.