Marcin Adamczyk Marcin Adamczyk - 9 days ago 5
JSON Question

Find object having the highest value in JSON in Postgresql

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

Answer

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)
Comments