时间戳

首页 > 解决方案 > Postgresql jsonb选择具有差异顺序的对象

postgresql - Postgresql jsonb选择具有差异顺序的对象

问题描述

我的表中有一个带有这个 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"}]}"

当我比较一个相等的对象但没有空格或其他顺序时，是否可以找到该行？样本

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}]'

是“相同”的对象，但顺序不同，并且元素之间的空间更小。

tks

标签： postgresqljsonb

解决方案

只需使用->which 给出 ajsonb而不是textand =。

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;

推荐阅读