Where in an Array JSON Postgres

I was able to resolve it as follows:

SELECT opcoes FROM pagina WHERE opcoes::jsonb @> '{"filtros":[{"valor":{"18":[]}}]}';

The trick here is that filters are an array, so you can not do the query directly, you have to go through each position of it.

For this, you should use the jsonb_array_elements(jsonb) function. It will transform the list of results into a "table" and you can give work with those results.

Imagine that you have instead of 1 element inside filters, have 2 elements. Example:

'{"filtros":[
    { 
        "tipo":"caracteristicas", 
        "operador":"=",  
        "valor":{"18":[12]}
    },
    { 
        "tipo":"caracteristicas", 
        "operador":"=",  
        "valor":{"19":[12]}
    }
]}'

With (opcoes->>'filtros')::jsonb you will choose all elements of this array. With jsonb_array_elements you will transform it into tuples:

SELECT jsonb_array_elements((opcoes->>'filtros')::jsonb) FROM pagina limit 1;
                    jsonb_array_elements
---------------------------------------------------------------------
 {"tipo": "caracteristicas", "valor": {"18": [12]}, "operador": "="}
 {"tipo": "caracteristicas", "valor": {"19": [12]}, "operador": "="}
(2 rows)

But you still have to see if one of these guys has the value "18". I imagine you wanted to look specifically at the valor field and not any field. For this you have to reference it in the query:

SELECT jsonb_array_elements((opcoes->>'filtros')::jsonb)->>'valor' FROM pagina limit 1;
   ?column?
--------------
{"18": [12]}
{"19": [12]}
(2 rows)

Now just see if the "value" has the key '18' using the operator ? (always remembering that you have to transform the result into a jsonb to allow the query).

SELECT (jsonb_array_elements((opcoes->>'filtros')::jsonb)->>'valor')::jsonb ? '18' FROM pagina limit 1;
   ?column?
--------------
t
f
(2 rows)

As this result can return more than one row for each stored tuple, you can put it in a subquery to do the filter, something like:

SELECT opcoes FROM pagina WHERE id in (
    SELECT DISTINCT id FROM pagina WHERE 
        (jsonb_array_elements((opcoes->>'filtros')::jsonb)->>'valor')::jsonb ? '18'
    );