postgresql & json - counting distinct values

Question

In PostgreSQL, I have a table that looks like,

| id | json                            |
| -- | ------------------------------- |
| 1  | {"id":1,"customer":"BANK"}      |
| 1  | {"id":1,"customer":"BANK"}      |
| 2  | {"id":2,"customer":"GOVT"}      |
| 3  | {"id":3,"customer":"BANK"}      |
| 4  | {"id":4,"customer":"ASSET MGR"} |
| 4  | {"id":4,"customer":"ASSET MGR"} |

I need the output of counting the occurrences of customers with unique ids, such as

| customer    | count |
| ----------- | ----- |
| "BANK"      | 2     |
| "GOVT"      | 1     |
| "ASSET MGR" | 1     |

Is there a good way to achieve using PostgreSQL & json? I currently am able to extract the customer and IDs, but am having difficulty counting the unique json objects.

eshirvana · Accepted Answer · 2021-08-03 18:18:01Z

2

select count(distinct id), jsondata ->> 'customer' customer 
from data
group by customer

count | customer 
----: | :--------
    1 | ASSET MGR
    2 | BANK     
    1 | GOVT

db<>fiddle here

answered Aug 3, 2021 at 18:18

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

postgresql & json - counting distinct values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related