✒️
Start your developer blog today!
✉️
Questions?
Email me

How To Query JSON Object with Unknown Keys in PostgreSQL

Apr 28, 2020 1 min read

TL;DR

-- Use jsonb_object_keys()
select col->jsonb_object_keys(col) from t;

Not Quite TL;DR

Suppose I have a jsonb column that stores other JSON objects with unknown keys for whatever reason. It could be because they’re user-generated, or maybe they’re timestamps.

Let’s go with timestamps.

{
    "0": {
        "id": "4d3",
        "class": 1
    },
    "120": { 
        "id": "5a7",
        "class": 2
    },
    "240": { 
        "id": "3b8",
        "class": 3
    }
}

We want to grab the class field of each object in column col of table t.

select col->jsonb_object_keys(col)->>'class' from t;

Let’s try this with this standalone WITH query.

with t(col) as (values('{
    "0": {
        "id": "4d3",
        "class": 1
    },
    "120": { 
        "id": "5a7",
        "class": 2
    },
    "240": { 
        "id": "3b8",
        "class": 3
    }
}'::jsonb))
select col->jsonb_object_keys(col)->>'class' from t;
?column?
1
2
3

View on DB Fiddle