How to filter on JSONB column

[dvd3v]

Hi!

In my table I have a JSONB column containing all the attributes per feature. The JSON is nicely encoded as feature attributes by ST_AsMVT, which works as expected:

The Mapbox Vector Tile format can store features with varying sets of attributes. To use this capability supply a JSONB column in the row data containing Json objects one level deep. The keys and values in the JSONB values will be encoded as feature attributes.

The attributes are showing up correctly in the rendered tiles. The question is regarding filtering these attributes. I've tried both cql2-text and cql2-json like:

params = {
    "filter-lang": "cql2-text",
    "filter": "foo=bar",
}

and

params = {
    "filter-lang": "cql2-json",
    "filter": json.dumps(
        {
            "op": "like",
            "args": [
                {"property": "foo"},
                "bar",
            ],
        }
    )
}

However, both approaches raise a TypeError(f"Field {name} not in table.")

What would be the best approach here? Thanks! 🙏

[vincentsarago]

@dvd3v

JSONB column containing all the attributes per feature

do you mean you have a feature's property (column) that is a JSON object and you want to use this properties.{field} in filter?

[vincentsarago]

ok so to be clear you have a dataset like

[
  {
    "collectionId": "public.my_data_json",
    "itemId": 1,
    "datetime": "2004-10-19T10:23:54",
    "id": "0",
    "ogc_fid": 1,
    "props": {
      "a": 1
    },
    "geometry": "SRID=4326;POLYGON((-10.803413563924646 80.9888,-8.97407 80.342,-9.179187820157681 80.32527435418599,-11.681552891305088 80.12122737355233,-16.174145376007445 79.75489396269606,-16.793424674574407 79.70439690593224,-16.9847 79.6888,-18.84147058372131 80.18684021124938,-19.019893692515183 80.23469851080159,-19.05658048771189 80.24453898295793,-20.58939158639589 80.65568377924396,-21.364427878882918 80.86357119528788,-21.941443004331376 81.01834353899686,-22.194578297841403 81.08624183303475,-22.2153 81.0918,-22.005462496557104 81.1096841708568,-21.787979773514834 81.1282199335945,-21.730125373347963 81.13315078704264,-16.94317356501963 81.54113631212135,-16.44643649320999 81.58347255096038,-13.716189764512416 81.81616784220274,-13.487838154016574 81.83562994573774,-13.2547 81.8555,-13.236084413047303 81.8489180971369,-11.681594487591145 81.29929794725759,-11.41038485572019 81.20340650654987,-10.803413563924646 80.9888))"
  }
]

(see props is a JSON object)

and you want to run something like

filter_query = {"op": "=", "args": [{"property": "props.a"}, 1]}

r = httpx.get(
    "http://127.0.0.1:8080/collections/public.my_data_json/items",
    params={
        "filter-lang": "cql2-json",
        "filter": json.dumps(filter_query)
    }
)

Sadly I don't think this is possible :-(

I've tried 👇 without success but I'm not a CQL2 expert, this will be a question for @bitner

filter_query = {
  "filter-lang": "cql2-json",
  "filter": {
    "op": "=",
    "args": [
      { 
        "function" : "json_extract_path", 
        "args" : [ { "property": "props" }, "a" ]
      },
      1
    ]
  }
}

[vincentsarago]

at least I found a bug in pygeofilter 😅 geopython/pygeofilter#76

FYI I've tried with

filter_query = {
  "op": "=",
  "args": [
    {
      "function": {
        "name": "jsonb_path_query",
        "arguments": [
          { "property": "props" }, "$props.a",
        ]
      }
    },
    1
  ]
}

r = httpx.get(
    "http://127.0.0.1:8080/collections/public.my_data_json/items",
    params={
        "filter-lang": "cql2-json",
        "filter": json.dumps(filter_query)
    }
)

but now I get {"detail":"set-returning functions are not allowed in WHERE"}

[dvd3v]

Alright thanks for looking into this! Sadly I run into the same error. So in theory it should be possible to filter on the properties using the json_path_query function, but its the pygeofilter bug that is causing the error?

[vincentsarago]

not really I fixed the pygeofilter bug locally but then I get {"detail":"set-returning functions are not allowed in WHERE"} so either the filter is not well formed or it's not possible using jsonb_path_query

[dvd3v]

Understood. Are there any possible workarounds using other methods or filters, perhaps the properties_filter?