0

So I've got this model with this JSONField - 

class Details(models.Model):
    ref_det = JSONField(null=True, default=dict())

ref_det stores values in this format

{'user_id': '2231', 'org_id': 'qpdkm12'}

Each object of the model has its ref_det as a dictionary of just those two values.

Now I have this list of user IDs - user_ids - and I want to get those objects from the model where the user_id key in its ref_det field contains any of those user_id in the user_ids list.

So if I have user_ids = ['2231', '973','431'], I should get those Details objects whose ref_det has its user_id as any of the 3 values in the list.

I tried looking up through contains, but I think that supports lookups of only a single value and not a list.

I think __values__contains (https://docs.djangoproject.com/en/1.9/ref/contrib/postgres/fields/#hstorefield) is appropriate for this usecase, but then again, I don't have an HStoreField.

Does anyone know how I can get through this?

Thanks!

Sidharth Samant
  • 714
  • 8
  • 28
  • 1
    Is a jsonfield the right choice here ? Since "Each object of the model has its ref_det as a dictionary of just those two values", a proper relational model would be much simpler to use and probably perform better. – bruno desthuilliers Oct 30 '18 at 09:52
  • @brunodesthuilliers - I agree, but the JSONField had been months earlier and is already being used in many places. So I thought it's better to work around it, rather than create a new model. – Sidharth Samant Oct 30 '18 at 10:12
  • I'm not talking about "creating a new model" but about modifying the existing one, which can be done easily using migrations. This of course requires changes to the client code (code using this model) but if it's known that you will always have those two keys and only those two keys in the jsonfield then fixing a wrong design is better than "working around" it, even if it requires a bit more work now (hint: it will certainly save time in the future). Just my 2 cents... – bruno desthuilliers Oct 30 '18 at 10:16
  • You're right. That's certainly better. – Sidharth Samant Oct 30 '18 at 10:51

1 Answers1

2

Django 1.10 or upper:

You can try like this:

 Details.objects.filter(ref_det__user_id__in=['2231', '973','431'])

Django 1.9:

You can try like this:

 list(filter(lambda x: x.ref_det.get('user_id') in ['2231', '973','431'], Details.objects.all()))  # it will return a list not queryset

FYI: I tried this with Django 1.10.8, but I think, you can do lookup operations like contains, in, isnull inside JSONField(reference).

ruddra
  • 50,746
  • 7
  • 78
  • 101
  • You are right, this solution does not work for Django 1.9. Let me see if there is other ways to do that – ruddra Oct 30 '18 at 09:43
  • So what if `ref_det` is a list of dictionaries, and you want to search for the user IDs in any of the dictionaries in the lists from the queryset? Is that possible? – shacker Nov 21 '19 at 00:05