how to fetch row on the basis of json data stored in a column in Postgresql

Question

I have a table like this

| 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    |

I want to fetch all duplicate row (here duplicate means rows containing is_selcted_cours is true and the value of date, amount in the content column have same value) like in this table selected rows will be 1,5,6,7

yes.. @a_horse_with_no_name

bala
– bala

2020-04-28 08:57:25 +00:00
Commented Apr 28, 2020 at 8:57 — bala
– bala, Commented Apr 28, 2020 at 8:57

user330315 · Accepted Answer · 2020-04-28 09:01:06Z

2

I would use an EXISTS condition for that:

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)

The d1.id <> d2.id is necessary to not compare a row with itself. id is assumed to be a primary (or unique) key column.

answered Apr 28, 2020 at 9:01

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

bala Over a year ago

can you please help me with one more thing the selected row from the above query i wanted to update the selected row with value 5 except any one row of each group which have whicch have same date amount and is_selected_cours true

Collectives™ on Stack Overflow

how to fetch row on the basis of json data stored in a column in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related