Get json element from JSON Array

Question

I have the sample data for demostration as shown below:

Table:

create table tbl_jdata
(
id int,
jdata json
);

insert into tbl_jdata values(1,'[{"salary":10000,"name":"mak"},{"salary":20000,"name":"jak"},{"salary":45000,"name":"abc"}]');

I want to display only a json element which is having highest salary for example as shown below in expected result.

Expected Result:

id    jdata
-------------------------------------
1     [{"salary":45000,"name":"abc"}]

My try:

select t.id,each_section
from tbl_jdata t
cross join unnest(t.jdata) each_section
where t.id = 1 and (each_section ->> 'salary') in 
(
 select max(each_section ->> 'salary')
 from tbl_jdata t
 cross join unnest(t.jdata) each_section
);

Getting an error:

ERROR: function unnest(json) does not exist

@a_horse_with_no_name, Sorry my bad, the actually datatype is json for jdata column and data has been stored in the given format. — MAK
– MAK, Commented May 13, 2021 at 15:24

user330315 · Accepted Answer · 2021-05-13 15:38:11Z

2

You need to use json_array_elements() rater than unnest() on a JSON array. You can use a lateral join to get the highest element after doing that:

select t.id, i.*
from the_table t
  cross join lateral (
     select x.item
     from json_array_elements(t.jdata) as x(item)
     order by (x.item ->> 'salary')::int desc
     limit 1
   ) i

Note that the use of jsonb is recommended over json

answered May 13, 2021 at 15:38

user330315

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

Comments

Enrique Arevalo · Accepted Answer · 2021-05-13 15:31:27Z

0

you need to use jsonb, here i am going to send you a references.

https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/

answered May 13, 2021 at 15:31

Enrique Arevalo

3333 silver badges8 bronze badges

Collectives™ on Stack Overflow

Get json element from JSON Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related