How To Query JSON Object with Unknown Keys in PostgreSQL
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 |