Select greatest number from a json list of variable length with PostgreSQL

Question

I have a column (let's call it jsn) in my database with json object (actually stored as plain text for reasons). This json object looks like this:

{"a":
    {"b":[{"num":123, ...},
          {"num":456, ...},
          ...,
          {"num":789, ...}],
     ...
    },
 ...
}

I'm interested in the biggest "num" inside that list of objects "b" inside the object "a". If the list if of known length I can do it like this:

SELECT
    GREATEST((jsn::json->'a'->'b'->>0)::int,
             (jsn::json->'a'->'b'->>1)::int,
             ... ,
             (jsn::json->'a'->'b'->>N)::int))
FROM table

Note that I'm new to PostgreSQL (and database querying in general!) so that may be a rubbish way to do it. In any case it works. What I can't figure out is how to make this work when the list, 'b', is of arbitrary and unknown length.

In case it is relevant, I am using PostgreSQL 10 hosted on AWS RDS and running queries using pgAdmin 4.

I can't think of any reason to store a JSON value in a text column. — user330315
– user330315, Commented Mar 12, 2019 at 12:53

user330315 · Accepted Answer · 2019-03-12 12:52:30Z

2

You need to unnest the array then you can apply a max() on the result:

select max((n.x -> 'num')::int)
from the_table t
  cross join jsonb_array_elements(t.jsn::jsonb -> 'a' -> 'b') as n(x);

you probably want to add a group by, so that you can distinguish rom which row the max value came from. Assuming your table has a column id that is unique:

select id, max((n.x -> 'num')::int)
from the_table t
  cross join jsonb_array_elements(t.jsn::jsonb -> 'a' -> 'b') as n(x)
group by id;

answered Mar 12, 2019 at 12:52

user330315

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

Collectives™ on Stack Overflow

Select greatest number from a json list of variable length with 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