Group Json array data with multiple fields using CROSS JOIN LATERAL on PostgreSQL

Question

This is jsonb field data:

[
   {'name': 'pratha', 'email': '[email protected]', 'sub': { 'id': 1 } },
   {'name': 'pratha', 'email': '[email protected]', 'sub': { 'id': 2 } }
]

When I want to get name and email fields, I get duplicate fields.

This is query:

SELECT jae.e->>'name', jae.e->>'email'
       FROM survey_results sr
            CROSS JOIN LATERAL jsonb_array_elements(sr.data_field) jae (e)
            GROUP BY jae.e->>'name', jae.e->>'email'

In this data, both objects' name is pratha but email is different. What I want to is grab one pratha and latest email in array.

Is it possible?

Actual Result:

pratha    [email protected]
pratha    [email protected]

Expected Result:

pratha    [email protected]

I only need name and email fields therefore, I want to group them by name.

See here: http://sqlfiddle.com/#!17/9b55f/2

Kaushik Nayak · Accepted Answer · 2019-05-24 03:49:23Z

1

You've not explained what "latest email" means, but I presume you want the record with the highest sub->id

You may use DISTINCT ON to get a highest in a group.

SELECT DISTINCT ON (jae.e->>'name') --for every 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 
                                         --^ return only the row with the highest id

DEMO

answered May 24, 2019 at 3:49

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Group Json array data with multiple fields using CROSS JOIN LATERAL on PostgreSQL

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