CODEWITHSUNDEEP
postgresqlpostgresql-11
MAK
MAK

Reputation: 7270

Get json element from JSON Array

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

Upvotes: 1

Views: 299

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 2

Enrique Arevalo
Enrique Arevalo

Reputation: 333

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/

Upvotes: 0

Related Questions