Query the contents of a Django model's JSONField

Question

I'm trying to figure out a way to query the data inside a postgres JSONField on a particular instance of my model.

From what I've seen, all the use cases are for things the equivalent of if you had an attributes JSONField, and then selecting all instances of your model where attributes ->> color = 'red' or whatever.

So let's give this a more real example, let's say we have a model Shoe and it has price and quantity and whatever as fields, but then also a JSONField called versions, which is an array of objects, each objects having the things that make each version special.

So if one instance of the Shoe model is Air Jordans, the attributes JSONField could look like:

[ 
    { 
        color: black, 
        edition: limited, 
        tongueColor: red 
    }, 
    { 
        color: black, 
        edition: standard 
    }, 
    { 
        color: gold, 
        edition: fancy, 
        bright: very 
    } 
]

So there's two things we can do, we can:

1. Query the Model for all shoes that have an available version with color: black (which would return our Air Jordans, plus maybe other shoes), or
2. Query the instance of the model for all objects inside the JSONField versions where color = black. So if we already had Shoe.objects.filter(name='Air Jordans') or something, could tack on some method or something at the end to just return the first two objects above where color == black?

I can find examples online of doing the first, but not the second.

I could just bring the object into memory and filter from there, but this JSONField is where I was hoping to store very large quantities of arbitrary data, and so being able to query without bringing the whole blob into memory is fairly important. Can JSONField support this?

Answer 1

@AntoinePinsard pointed me in the right direction - json_to_recordset().

I'm currently using something like:

SELECT * FROM (
    SELECT j.* from shoes, json_to_recordset(json_field_name) as 
    j(color text, edition text, tongueColor: text, bright text) where 
    shoes.shoe_name = 'Air Jordan'
) subset
WHERE subset.color= "black"

So the inner select statement will build internally a recordset that looks like this:

color | edition  | tongueColor | bright
------+----------+-------------+--------
black | limited  | red         |
black | standard |             |
gold  | fancy    |             | very

and then the outer statement will query that internal recordset (in this case, where color = 'black' and will return:

color | edition  | tongueColor | bright
------+----------+-------------+--------
black | limited  | red         |
black | standard |             |

Thanks for the help everyone!