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!

Ozgur Akcali · Accepted Answer · 2019-04-02T09:07:53.183

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.

Hi @Ozgur_Akcali, thanks very much for your answer, I will give it a try now! But I actually do have control over the database and model structure. Could you please leave your current answer, but also update it with how a better structure would be to achieve this? I would really appreciate it. It would help my understanding. — ianbroad, Apr 02 '19 at 03:08
@ianbroad I have updated my anser to include a solution with an updated model structure — Ozgur Akcali, Apr 02 '19 at 09:08
Sorry it took awhile to test, but worked great, thanks so much. — ianbroad, Apr 05 '19 at 04:32

score 0 · Answer 2 · answered Mar 29 '19 at 22:36

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.

score 0 · Answer 3 · answered Mar 29 '19 at 22:42

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..

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

3 Answers3