SQLAlchemy: filtering on values stored in nested list of the JSONB field
<p>Lets say I have a model named <code>Item</code>, which contains a JSONB field <code>data</code>. One of the records has the following JSON object stored there:</p>

<pre><code>{
    "name": "hello",
    "nested_object": {
        "nested_name": "nested"
    },
    "nested_list": [
        {
            "nested_key": "one"
        },
        {
            "nested_key": "two"
        }
    ]
}
</code></pre>

<p>I can find this record by filtering on the <code>name</code> field as such:</p>

<pre><code>Session().query(Item).filter(Item.data["name"] == "hello")
</code></pre>

<p>I can find this record by filtering on the nested object likewise as such:</p>

<pre><code>Session().query(Item).filter(Item.data[("nested_object","nested_name")] == "hello")
</code></pre>

<p>However I am struggling to find a way to find this record by filtering on the value of the item stored within the nested list.
In other words I want to find the record above if the user has provided value "one", and I know to look for it in the key <code>nested_key</code> within the <code>nested_list</code>.</p>

<p>Is it possible to achieve this with SQLAlchemy filters available?</p>

<p>SQLAlchemy's <a href="http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB" rel="nofollow"><code>JSONB</code></a> type has the <a href="http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB.Comparator.contains" rel="nofollow"><code>contains()</code></a> method for the <code>@&gt;</code> operator in Postgresql. <a href="https://www.postgresql.org/docs/9.5/static/functions-json.html" rel="nofollow">The <code>@&gt;</code> operator</a> is used to check if the left value contains the right JSON path/value entries at the top level. In your case</p>

<pre><code>data @&gt; '{"nested_list": [{"nested_key": "one"}]}'::jsonb
</code></pre>

<p>Or in python</p>

<pre><code>the_value = 'one'

Session().query(Item).filter(Item.data.contains(
    {'nested_list': [{'nested_key': the_value}]}
))
</code></pre>

<p>The method converts your python structure to suitable JSON string for the database.</p>

python json postgresql sqlalchemy