How to deep query JSONField

Using Django Using the ORM
1

Suppose I have a JSONField which is filled, in most records, with data such as this:

{
  "n1": ["0123", "4567", "8901"],
  "n2": ["1234", "5678", "9012"],
  "n3": ["2345", "6789", "0123"]
}

That is, dict[str,list[str]]. How could I perform a query in order to retrieve all records where any element of the “n1” key start with “0”, for instance? How should I define the filter parameters for such a query?

2

This is covered in the Querying JSONField docs, with examples.

3

Hello, I read that doc page, but I couldn’t find a solution for my specific case. I know I could use something like .filter(myfield__n1__0__startswith="0") to check the first element, but I didn’t get how to do something like this: .filter(myfield__n1__<any>__startswith="0"). I’m sorry if I might have skipped it, but I really can’t find a way to do it, thus I’m asking for help.

4

Ok, I can see the issue here.

Actually, I’m looking at the PostgreSQL docs for querying JSON data, and I’m not sure that there’s a fundamental way of doing this. Do you have a raw SQL statement capable of this type of query?

5

No, the use case came up for an application that’s already implemented in Django, so I never implemented it directly in raw SQL. If there’s no direct way to perform such a query, I guess I’ll have to do an indirect approach, loading all n1 sets together with their record’s primary key, then selecting those who satisfy my condition, and then loading the records using the primary key. But that seems expensive and cumbersome, so I was trying to avoid it, if possible. Thank you anyway, for considering my problem! :slight_smile:

6

Clarification - I didn’t say it wasn’t possible, just that I’m not aware of one.

I have found a raw SQL query that should work:

select distinct * 
from my_model, jsonb_array_elements_text(my_json_field->'n1') elem
where elem like '0%';

However, I’ve been unable to produce a pure ORM solution. The closest I’ve come involves creating a queryset containing a cartesian product of the number of rows times the number of elements in ‘n1’, which isn’t going to be practical for a model of any significant size.

1 Like
7

I see. Perhaps I could try writing some specific custom lookups for JSONField. But I have to learn a lot before I can do that. Thank you very much for the explanation.

8

This is the query that would do what I need. I’ll be honest, though. ChatGPT wrote it for me, I couldn’t do it myself right now:

SELECT id, numeros
FROM correio_mensagem
WHERE EXISTS (SELECT 1
              FROM jsonb_array_elements_text(numeros -> 'nup') AS element
              WHERE element LIKE '0%');
9

Yea, that’s another way to do it. This version however doesn’t return the entire row - just the pk. (Either version could be modified to return what the other returns.)

It would be interesting to check both versions with an explain analyze on a real-life table to see how they each perform.

1 Like