How can I check if an array field contains a certain value OR the array field is null?

[chrispy-snps]

I have a set of documents. Some documents are versioned; others are not. A single document can belong to multiple versions.

In my document database, relevant versions are stored in a JSON field. For example,

import ibis

ibis.options.interactive = True

ddb = ibis.duckdb.connect("mydb.duckdb")

schema = ibis.schema(
    {
        "id": "int64",
        "json_data": "json",
    }
)

ddb.drop_table("mytable", force=True)
ddb.create_table("mytable", schema=schema)

data = [
    {"id": 1, "json_data": {"versions": ["v1", "v2"]}},
    {"id": 2, "json_data": {"versions": ["v3"]}},
    {"id": 3, "json_data": {}},
]

ddb.insert("mytable", data)

t = ddb.table("mytable")

t.select(
    t.id,
    t.json_data["versions"],
)

┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ id    ┃ JSONGetItem(json_data, 'versions') ┃
┡━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ int64 │ json                               │
├───────┼────────────────────────────────────┤
│     1 │ ['v1', 'v2']                       │
│     2 │ ['v3']                             │
│     3 │ NULL                               │
└───────┴────────────────────────────────────┘

I can filter for documents where version contains v2 or v3:

t.filter(
    t.json_data["versions"].cast("array<str>").contains("v2")
    | t.json_data["versions"].cast("array<str>").contains("v3")
)

┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ id    ┃ json_data           ┃
┡━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ int64 │ json                │
├───────┼─────────────────────┤
│     1 │ {'versions': [...]} │
│     2 │ {'versions': [...]} │
└───────┴─────────────────────┘```

and I can filter for documents that are "unversioned" (thus being relevant to all versions):

t.filter(
    t.json_data["versions"] == None
)

┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┓
┃ id    ┃ json_data          ┃
┡━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ int64 │ json               │
├───────┼────────────────────┤
│     3 │ {'versions': None} │
└───────┴────────────────────┘

But how do I get all documents that are relevant to a specific version, or are unversioned (and thus still relevant)?

t.filter(
    t.json_data["versions"].cast("array<str>").contains("v1")
    | t.json_data["versions"] == None
)

--------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[71], [line 2](vscode-notebook-cell:?execution_count=71&line=2)
      [1](vscode-notebook-cell:?execution_count=71&line=1) t.filter(
----> [2](vscode-notebook-cell:?execution_count=71&line=2)     t.json_data["versions"].cast("array<str>").contains("v1")
      [3](vscode-notebook-cell:?execution_count=71&line=3)     | t.json_data["versions"] == None
      [4](vscode-notebook-cell:?execution_count=71&line=4) )

TypeError: unsupported operand type(s) for |: 'BooleanColumn' and 'JSONColumn'

(And, am I thinking about method chaining in the right way for JSON-based arrays, or am I missing some fundamentally better approach?)

Thanks in advance!

[cpcloud]

You're missing the () around the t.json_data["versions"] == None.

The | operator binds tighter than ==, so the parentheiszation (the way the Python interpreter sees it) of what you've written is actually

(t.json_data["versions"].cast("array<str>").contains("v1") | t.json_data["versions"]) == None

You'll need to parenthesize any binary operators in the specific way that you want to evaluate them when any of the bitwise operators (|, &, and ^) are in an expression.

In this case that'd be:

t.json_data["versions"].cast("array<str>").contains("v1") | (t.json_data["versions"] == None)

Running that in IPython:

In [14]: t.json_data["versions"].cast("array<str>").contains("v1") | (t.json_data["versions"] == None)
Out[14]:
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Or(ArrayContains(Cast(JSONGetItem(json_data, 'versions'), array<string>), 'v1'), IsNull(JSONGetItem(json_data, 'versions'))) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ boolean                                                                                                                      │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ True                                                                                                                         │
│ False                                                                                                                        │
│ True                                                                                                                         │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Alternatively, you can use the isnull() method, which wouldn't require parentheses because there are binary operators involved in using it.

t.json_data["versions"].cast("array<str>").contains("v1") | t.json_data["versions"].isnull()

This is:

1. Not something Ibis can do anything about, it's part of the Python grammar and therefore baked into the language.
2. Unfortunately a common pitfall for folks who've not yet had to deal with it, and/or are coming from other data analysis platforms like MATLAB and R where == operator binds tighter than the the | operator.

[chrispy-snps]

A complete and clear answer - thank you! I was too distracted with thinking about methods to also consider the usual expression evaluation stuff. Now it makes sense.

A follow-up question - logical Boolean operators have a lower precedence than comparison operators, but they don't seem to be supported:

t.filter(
    (t.json_data["versions"].cast("array<str>").contains("v1"))
    or
    (t.json_data["versions"] == None)
)

...trace omitted...
ValueError: The truth value of an Ibis expression is not defined

I don't see an enhancement issue filed for this. Is this a reasonable request to file, or is it not already implemented for logistical reasons?

[cpcloud]

Python itself doesn't allow overloading or, and, or not, so the best we can do is to overload |, &, and ~.