Assume we have couple of objects in database with attribute data where attribute data consists: {'gender' => {'male' => 40.0, 'female' => 30.0 => 'undefined' => 30.0}}.
I would like to find only these objects, which have the gender => male value the highest.
PostgreSQL 9.5
Assuming I understand your question correctly (example input/output would be useful):
WITH jsons(id, j) AS (
VALUES
(1, '{"gender": {"male": 40.0, "female": 30.0, "undefined": 30.0}}'::json),
(2, '{"gender": {"male": 40.0, "female": 30.0, "undefined": 30.0}}'),
(3, '{"gender": {"male": 0.0, "female": 30.0, "undefined": 30.0}}')
)
SELECT id, j
FROM jsons
WHERE (j->'gender'->>'male') :: float8 = (
SELECT MAX((j->'gender'->>'male') :: float8)
FROM jsons
)
;
┌────┬───────────────────────────────────────────────────────────────┐
│ id │ j │
├────┼───────────────────────────────────────────────────────────────┤
│ 1 │ {"gender": {"male": 40.0, "female": 30.0, "undefined": 30.0}} │
│ 2 │ {"gender": {"male": 40.0, "female": 30.0, "undefined": 30.0}} │
└────┴───────────────────────────────────────────────────────────────┘
(2 rows)
select * from your_table where 'male' = (select k from json_each_text(data->'gender') as j(k,v) order by v::numeric desc limit 1);