Is there a way to filter by the content of JSON lists?

[piterdias]

Environment

• PostgreSQL version: 12.5
• PostgREST version: 7.0.1
• Operating system: GNU/Linux Ubuntu 20.04 LTS

Description of issue

I have a JSONB column (detalhe_objeto) for complex data (financial instruments, for example) and PostgREST is working like a charm. There are keys in the JSON document, like Estrategias below, that are lists. Is there a way to use "Estrategias" contents in a filter? I would like to return, for example, rows where View 1 is in detalhe_objeto->Parametros Basicos->Estrategias.

Can PostgREST do it (as it is)?

Current Returned Data

[
    {
        "id_objeto": "518eb02d-08bd-4bff-a4da-13e9fe3c7b92",
        "id_tipo_objeto": "b818baf8-e542-4af0-a8ee-8ffcb2ae465a",
        "detalhe_objeto": {
            "Objeto": "Portfolio 1",
            "Tipo de Objeto": "Portfolio",
            "Parametros Basicos": {
                "Estrategias": [
                    "View 1",
                    "View 2"
                ]
            }
        }
    },
    {
        "id_objeto": "c1c37ecb-8a21-4848-a173-53fe3b8efdbd",
        "id_tipo_objeto": "b818baf8-e542-4af0-a8ee-8ffcb2ae465a",
        "detalhe_objeto": {
            "Objeto": "Portfolio 1",
            "Tipo de Objeto": "Portfolio",
            "Parametros Basicos": {
                "Estrategias": [
                    "View 1"
                ]
            }
        }
    },
    {
        "id_objeto": "e76c7156-9656-4efc-b59e-e2691e95176d",
        "id_tipo_objeto": "b818baf8-e542-4af0-a8ee-8ffcb2ae465a",
        "detalhe_objeto": {
            "Objeto": "Portfolio 1",
            "Tipo de Objeto": "Portfolio",
            "Parametros Basicos": {
                "Estrategias": [
                    "View 2"
                ]
            }
        }
    }
]

Desired Data

[
    {
        "id_objeto": "518eb02d-08bd-4bff-a4da-13e9fe3c7b92",
        "id_tipo_objeto": "b818baf8-e542-4af0-a8ee-8ffcb2ae465a",
        "detalhe_objeto": {
            "Objeto": "Portfolio 1",
            "Tipo de Objeto": "Portfolio",
            "Parametros Basicos": {
                "Estrategias": [
                    "View 1",
                    "View 2"
                ]
            }
        }
    },
    {
        "id_objeto": "c1c37ecb-8a21-4848-a173-53fe3b8efdbd",
        "id_tipo_objeto": "b818baf8-e542-4af0-a8ee-8ffcb2ae465a",
        "detalhe_objeto": {
            "Objeto": "Portfolio 1",
            "Tipo de Objeto": "Portfolio",
            "Parametros Basicos": {
                "Estrategias": [
                    "View 1"
                ]
            }
        }
    }
]

[wolfgangwalther]

Two ideas here:

• You could try to use arrow operators and @>, which is mapped to cs (contains). According to the PostgreSQL docs, you'd need to pass a json array, not an element here. So maybe something like this: GET /table_name?detalhe_objeto->Parametros Basicos->Estrategias=cs.["View 1"] - you'd probably need to percent-encode some of those characters. The space for sure and maybe also the [ ].
• I'd just use a computed column, which returns the "Estrategias" as a proper postgres array - and then use something like this: GET /table_name?estrategias=cs.{View 1}. Much less quoting and escaping/percent-encoding needed here.

[piterdias]

The first idea worked very well. Thanks a lot.