CODEWITHSUNDEEP
jsonpostgresql
Dennis
Dennis

Reputation: 1975

Count occurences along with result using DISTINCT ON on PostgreSQL

I have data like this:

   [
    {"name": "pratha", "email": "[email protected]", "sub": { "id": 1 } },
    {"name": "john", "email": "[email protected]", "sub": { "id": 5 } },
    {"name": "pratha", "email": "[email protected]", "sub": { "id": 2 } }
   ]

This is my query to get unique and latest emails:

SELECT DISTINCT ON (jae.e->>'name') 
    jae.e->>'name'  as name, 
    jae.e->>'email' as email
       FROM survey_results sr
            CROSS JOIN LATERAL jsonb_array_elements(sr.data_field)  jae (e)
         ORDER BY jae.e->>'name', jae.e->'sub'->>'id' desc

Problem is, when I add count(*) to select, all counts are equal.

I want to get unique result with distinct, and count their occurrences. So in this case, pratha should be 2 and john should be 1 with their data (not just counts)

How can achieve this with PostgreSQL?

See here: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f5c640958c3e4d594287632d0f4a835f

Upvotes: 0

Views: 47

Answers (1)

Oto Shavadze
Oto Shavadze

Reputation: 42763

Do you need this? 

SELECT DISTINCT ON (jj->>'name')  jj->>'name',  jj->>'email' , count(*) over(partition by jj->>'name' )
from survey_results
join lateral jsonb_array_elements(data_field) j(jj) on true
ORDER BY jj->>'name', jj->'sub'->>'id'  desc

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=5f07b7bcb0001ebe32aa2f1338d9d0f0

Upvotes: 1

Related Questions