Postgresql json query

Question

I have one table with one column jsonb with the text:

[
  {
    "id": 1,
    "nome": "AUTOMÓVEL",
    "partes": [
      {
        "fracao": 100,
        "documento": "111.111.111-11"
      }
    ],
    "atributos": [
      {
        "id": 3,
        "nome": "MARCA",
        "valor": "FORD"
      },
      {
        "id": 2,
        "nome": "MODELO",
        "valor": "FIESTA"
      },
      {
        "id": 1,
        "nome": "RENAVAM",
        "valor": "RENAVAM"
      }
    ]
  },
  {
    "id": 1,
    "nome": "AUTOMÓVEL",
    "partes": [
      {
        "fracao": 100,
        "documento": "111.111.111-11"
      }
    ],
    "atributos": [
      {
        "id": 3,
        "nome": "MARCA",
        "valor": "FIAT"
      },
      {
        "id": 2,
        "nome": "MODELO",
        "valor": "PALIO"
      },
      {
        "id": 1,
        "nome": "RENAVAM",
        "valor": "RENAVAM"
      }
    ]
  },
  {
    "id": 1,
    "nome": "AUTOMÓVEL",
    "partes": [
      {
        "fracao": 100,
        "documento": "111.111.111-11"
      }
    ],
    "atributos": [
      {
        "id": 3,
        "nome": "MARCA",
        "valor": "HONDA"
      },
      {
        "id": 2,
        "nome": "MODELO",
        "valor": "FIT"
      },
      {
        "id": 1,
        "nome": "RENAVAM",
        "valor": "RENAVAM"
      }
    ]
  }
]

so i create a select:

select objetos
from protocolo 
where objetos @> '[{"id": 1 , "atributos": [{"nome": "MARCA", "valor" : "FIAT"}]}]'

but this select will return the column with all text, but I need to return only the element that match in my where clause

so I need to return only this:

{
    "id": 1,
    "nome": "AUTOMÓVEL",
    "partes": [
      {
        "fracao": 100,
        "documento": "111.111.111-11"
      }
    ],
    "atributos": [
      {
        "id": 3,
        "nome": "MARCA",
        "valor": "FIAT"
      },
      {
        "id": 2,
        "nome": "MODELO",
        "valor": "PALIO"
      },
      {
        "id": 1,
        "nome": "RENAVAM",
        "valor": "RENAVAM"
      }
    ]
  }

Answer 1

Use the function jsonb_array_elements():

select value
from protocolo
cross join lateral jsonb_array_elements(objetos)
where value @> '{"id": 1 , "atributos": [{"nome": "MARCA", "valor" : "FIAT"}]}';

Note that the json expression in the where clause has changed.