I have the following table:

create table subscriptions (id int, data jsonb);

filled with the following data:

insert into subscriptions (id, data) 
values 
  (1, '{"versions": {"10.2.3": 3, "9.2.3": 4, "12.2.3": 5, "1.2.3": 5}}'),
  (2, '{"versions": {"0.2.3": 3, "2.2.3": 4, "3.2.3": 5}}');

And I am trying to write a query that will result in:

 -----------------
| id | minVersion |
 -----------------
| 1  | 1.2.3      |
| 2  | 0.2.3      |
 -----------------

What I have tried:

select
      (string_to_array(jsonb_object_keys((data->'versions')::jsonb), '.'))[1] as total_prices
from subscriptions;

(does not work but I think those methods will be useful here)

CodePudding user response：

I would create a function that extracts the version keys and sorts them to pick the smallest.

create or replace function smallest_version(p_version jsonb)
  returns text
as
$$
  select version
  from jsonb_each_text(p_version) as x(version, num)
  order by string_to_array(x.version, '.')::int[]
  limit 1
$$
language sql
immutable;

The above will only work if there are no versions like 1.2.3-beta or other non-numeric characters in your version number.

Then use it like this:

select smallest_version(data->'versions')
from subscriptions;

Online example