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

Question

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

Answers 1
Added at 2016-11-15 07:11
Tags
Question

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

Answers to

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

nr: #1 dodano: 2016-11-15 08:11

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;

In PostgreSQL there are table functions, functions that return a set of rows, like jsonb_each(). You should use these functions in the FROM list. These table functions can implicitly refer to columns from tables defined earlier in the list, like FROM my_table, jsonb_each(my_table.data), in which case a link between the two sources is made as if a join condition were specified between the two; in practice, the function gets called once for each of the rows of the source table and the function output is added to the list of available columns.

The JSON functions work only on the level of the JSON document that is explicitly specified. That could be the entire document (my_table.data in this case) or down to some path that you can specify. I am assuming here that the first key is a date value and that you therefore do not know the key in advance. The same goes for the sub-document. In these cases you use functions like jsonb_each(). The array position you apparently know exactly, so you can just index the array to find the price information. Note that these are apparently also in JSON format, so you should get the price as a text value with the ->> operator and then cast that to numeric so you can feed it to the min() function.

Source Show
◀ Wstecz