sql - 如何根据存储在 Postgresql 列中的 json 数据获取行

Question

我有一张这样的 table

| is_selcted_cours   | content                                     | value |
|   (boolean)        |(json)                                       |       |
------------------------------------------------------------------------------
|    true   | {"date":"07-Apr-2020","amount":"5050","type":"CT"}    |   1    |
|    false  | {"date":"07-Jun-2020","amount":"50","type":"CT"}      |   1    |
|    true   | {"date":"10-Aug-2020","amount":"6050","type":"MT"}    |   1    |
|    false  | {"date":"07-Jun-2020","amount":"50","type":"CT"}      |   2    |
|    true   | {"date":"07-Apr-2020","amount":"5050","type":"GT"}    |   3    |
|    true   | {"date":"07-Apr-2020","amount":"5050","type":"GT"}    |   3    |
|    true   | {"date":"07-Apr-2020","amount":"5050","type":"GT"}    |   3    |

我想获取所有重复的行(这里重复意味着包含 is_selcted_cours is true 和 the value of date, amount in the content column 的行具有相同的值)就像在这个表中选择的行将是 1,5,6,7

Answer 1

我会为此使用 EXISTS 条件:

select d1.*
from data d1
where is_selcted_cours
and exists (select *
            from data d2
            where d1.content ->> 'date' = d2.content ->> 'date'
              and d1.content ->> 'amount' = d2.content ->> 'amount'
              and d2.is_selcted_cours = d1.is_selcted_cours
              and d1.id <> d2.id)

d1.id <> d2.id有必要不将一行与其自身进行比较。 id假定为主(或唯一)键列。