Search code examples
postgresqljsonb

Postgresql jsonb select object with dif order

I have one column in my table with this jsonb

{"parcelas": "[{"valor": "2136,45", "parcela": 75, "vencimento": "15/06/2019"}, {"valor": "2097,61", "parcela": 76, "vencimento": "15/07/2019"}, {"valor": "2058,33", "parcela": 77, "vencimento": "15/08/2019"}, {"valor": "2191,07", "parcela": 78, "vencimento": "15/09/2019"}]}"

It`s possible to find that row when I compare a equal object but without space or another order? sample

SELECT * FROM myTable where myJsonBField ->> 'parcelas' = '[{"vencimento":"15/06/2019","valor":"2136,45","parcela":75},{"vencimento":"15/07/2019","valor":"2097,61","parcela":76},{"vencimento":"15/08/2019","valor":"2058,33","parcela":77},{"vencimento":"15/09/2019","valor":"2191,07","parcela":78}]'

is the "same" object but in another order and with less space between itens.

tks

Solution

  • Just use -> which gives a jsonb rather than a text and =.

    SELECT *
       FROM mytable
       WHERE myjsonbfield->'parcelas' = '[{"vencimento":"15/06/2019","valor":"2136,45","parcela":75},{"vencimento":"15/07/2019","valor":"2097,61","parcela":76},{"vencimento":"15/08/2019","valor":"2058,33","parcela":77},{"vencimento":"15/09/2019","valor":"2191,07","parcela":78}]'::jsonb;