Question: How to find the key for the minimum value in jsonb column of postgres?

Question

How to find the key for the minimum value in jsonb column of postgres?

Answers 1
Added at 2016-12-19 10:12
Tags
Question

I need to find the key of the minimum value in a jsonb object,I have found out minimum value, need to find the key of the same in the same query.

Query I am using

SELECT id,min((arr ->> 2)::numeric) AS custom_value 
FROM ( 
   SELECT id, jdoc
   FROM table, 
        jsonb_each(column1) d (key, jdoc)
   ) sub,
     jsonb_each(jdoc) doc (key, arr) 
group by 1 
Answers to

How to find the key for the minimum value in jsonb column of postgres?

nr: #1 dodano: 2016-12-19 11:12

This will do the job.
The left join ... on 1=1 is for keeping IDs with empty json

select      t.id
           ,j.key
           ,j.value

from                            mytable t

            left join lateral  (select      j.key,j.value 
                                from        jsonb_each(column1) as j 
                                order by    j.value 
                                limit       1
                                ) j

            on 1=1
Source Show
◀ Wstecz