Crazy Pixel Crazy Pixel - 20 days ago 10
JSON Question

How to find the minimum value in a jsonb data using postgres?

I have this jsonb data format-


data


{"20161110" : {"6" : ["12", "11", "171.00"],"12" : ["13", "11", "170.00"],"18" : ["16", "11", "174.00"]}}


I want to find the minimum value out of the prices,
In this case-170.00 .
I have tried indexing but able to find data for specific terms(6,12,18) but not the minimum out of them.


What I have tried-


data::json->(select key from json_each_text(data::json) limit 1))::json#>>'{6,2}'


which gives me result for 6th term that is 171.00

Answer

If you want the minimum value of the third element in the arrays, then you will have to unpack the JSON document to get to the array to compare values. That goes somewhat like this (and assuming you indeed have a jsonb column and a primary key called id):

SELECT id, min((arr ->> 2)::numeric) AS min_price
FROM ( SELECT id, jdoc
       FROM my_table, jsonb_each(data) d (key, jdoc) ) sub,
     jsonb_each(jdoc) doc (key, arr)
GROUP BY 1;
Comments