Where in a JSON Postgres Array

Question

How can I select only the records that have the value "18" in this JSON?

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

I tried to use the comparison with the @> but it didn’t work out because he didn’t return the record.

SELECT opcoes FROM pagina WHERE (opcoes->>'filtros')::jsonb @> '[18]'

Answer 1

The tricky thing here is that filters is an array, so you can’t do the query directly, you have to scroll to each position of it.

For this, you must use the function jsonb_array_elements(jsonb). It will turn the results list into a "table" and you can give work with these results.

Imagine 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 turn 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 "18" value. I imagine you wanted to look specifically at the field valor 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 '18' key using the operator ? (always remembering that you have to turn 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)

Since this result can return more than one row for each stored tuple, you can place it on a sub-allowance to make 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'
    );

Answer 2

I was able to solve it this way:

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