Hi!
I would like to create a JSON structure of chosen objects (animals) with
corresponding events in one database hit.
The model represents animals and different types of events that happened to
them:
class Animal(models.Model):
life_number = models.TextField(primary_key=True)
birthday = models.DateField(blank=True, null=True)
class
Inseminacja(models.Model):
cow = models.ForeignKey(Animal, models.DO_NOTHING,
db_column='cow_life_number')
date =
models.DateTimeField()
number =
models.IntegerField()
class WynikBadania(models.Model):
animal = models.ForeignKey(Animal, models.DO_NOTHING,
db_column='anilifenumber')
date_entered = models.DateTimeField(db_column='timestamp')
I use my JsonAgg and JsonBuildObject functions submitted to Trac at:
https://code.djangoproject.com/ticket/26327#no1
>>> qs = Animal.objects.filter(pk='PL005257066205').annotate(
ins =
JsonAgg(JsonBuildObject([F('inseminacja__date'),F('inseminacja__number')],
output_field=CharField())),
examinations =
JsonAgg(JsonBuildObject([F('wynikbadania__date_entered')],
output_field=CharField()))
)
The resulting query naturally results in cartesian product of the events,
which is exactly my problem:
>>> str(qs.query)
'SELECT
"animal"."life_number",
"animal"."birthday",
JSON_AGG(JSON_BUILD_OBJECT(\'timestamp\',"wynikbadania"."timestamp"))
AS "examinations",
JSON_AGG(JSON_BUILD_OBJECT(\'date\',"inseminacja"."date",\'number\',"inseminacja"."number"))
AS "ins"
FROM "animal"
LEFT OUTER JOIN "wynikbadania" ON ("animal"."life_number" =
"wynikbadania"."anilifenumber")
LEFT OUTER JOIN "inseminacja" ON ("animal"."life_number" =
"inseminacja"."cow_life_number")
WHERE "animal"."life_number" = PL005257066205
GROUP BY "animal"."life_number"'
>>> qs.values()
[{
'examinations': [
{u'timestamp': u'2014-11-20T12:41:54'},
{u'timestamp': u'2014-11-20T12:41:54'},
{u'timestamp': u'2015-07-13T11:41:08.40319'},
{u'timestamp': u'2015-07-13T11:41:08.40319'}
],
'birthday': None,
'ins': [
{u'date': u'2013-11-16T15:00:00', u'number': 1},
{u'date': u'2013-12-09T10:00:00', u'number': 2},
{u'date': u'2013-11-16T15:00:00', u'number': 1},
{u'date': u'2013-12-09T10:00:00', u'number': 2}
],
'life_number': u'PL005257066205'
}]
I don't know exact SQL query that should be used.
Using DISTINCT is not possible, as "JSON_AGG(DISTINCT JSON_BUILD_OBJECT("
results in "ERROR: could not identify an equality operator for type json".
Using GROUP BY "animal"."life_number",
"wynikbadania"."timestamp","inseminacja"."date" naturally results in 4
separate records.
Do you have any ideas?
Best wishes,
Tomasz
W dniu piątek, 4 marca 2016 18:51:51 UTC+1 użytkownik Simon Charette
napisał:
>
> Hi Thomasz,
>
> Using extra() is a dead end as it's planned to be removed.
>
> It's hard for me to guide you about how you should use the expression API
> with no
> model definition or queryset.
>
> Could you provide us with the set of model and the annotated querysets
> you're building?
>
> The queryset's SQL representation would also be useful
> (str(queryset.query)).
>
> Cheers,
> Simon
>
> Le vendredi 4 mars 2016 09:57:43 UTC-5, Tomasz Nowak a écrit :
>>
>> Hi Simon, thank you for your suggestion!
>> I managed to accomplish that for one column with custom ROW function and
>> JSON version with JSON_AGG and JSON_BUILD_OBJECT.
>>
>> However when using two such annotations the number of aggregated elements
>> gets multiplied by the number of elements in the other relation.
>> I found a different way to get such aggregations independently:
>> http://dba.stackexchange.com/a/130212/88817
>> but when I use extra(select={...}) the same SQL code is placed in GROUP
>> BY clause, which results in:
>>
>> ProgrammingError: could not identify an equality operator for type json
>>
>> How to prevent adding this extra select to GROUP BY? Without it the query
>> works well.
>>
>> Best wishes
>> Tomasz
>>
>>
>> W dniu wtorek, 1 marca 2016 03:52:58 UTC+1 użytkownik Simon Charette
>> napisał:
>>>
>>> Hi Tomasz,
>>>
>>> I suppose you could implement `ROW` as a custom expression[1] and use it
>>> in
>>> your `ArrayAgg` aggregate as follow:
>>>
>>> from django.contrib.postgres.aggregates import ArrayAgg
>>> from django.db.models import Expression
>>>
>>> class Row(Expression):
>>> template = 'ROW(%(expressions)s)'
>>>
>>> # ...
>>>
>>> MyModel.objects.aggregate(
>>> ArrayAgg(Row(F('field1'), F('field2'))),
>>> )
>>>
>>>
>>> Cheers,
>>> Simon
>>>
>>> [1]
>>> https://docs.djangoproject.com/en/1.9/ref/models/expressions/#writing-your-own-query-expressions
>>>
>>> Le lundi 29 février 2016 19:31:15 UTC-5, Tomasz Nowak a écrit :
>>>>
>>>> Hi!
>>>>
>>>> Module django.contrib.postgres.aggregates provides classes for
>>>> ARRAY_AGG and STRING_AGG PostgreSQL aggregates.
>>>>
>>>> Is it possible to provide more than one field to these aggregates?
>>>> Like in SQL, where you can provide more fields ("row" is optional):
>>>>
>>>> ARRAY_AGG(row(table.field1, table.field2))
>>>>
>>>> Best wishes,
>>>> Tomasz
>>>>
>>>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/404c1cbe-8bad-44a3-b5be-537e3130743d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
