I have a PostgreSQL (V14) database containing info in JSONB format. The info of one cell could be something like this:
{
"Car23": {
"color": "blue",
"year": 1982,
"engine": [
12,
23.3
],
"broke": [
2,
8.5
]
},
"Banana": {
"color": "yellow",
"year": 2022,
"taste": "ok"
},
"asdf": {
"taste": "bad",
"year": [
1945,
6
],
"engine": [
24,
53.534
]
},
"Unique": {
"broke": [
342,
2.5
]
}
}
The outer key, i.o "Car23" or "Banana" has a random name created by an outside program. I want to do queries that allow me to get where the outer key contains a certain key:value.
For instance:
In Sql this seems pretty standard stuff, however I don't know how to do this within JSONB when the outer keys have random names...
I red that outer wildcard keys aren't possible, so I'm hoping there's another way of doing this within Postgresql.
You will need to unnest the JSON elements and then pick the ones you want. The fact that some values are sometimes stored in an array, and sometimes as a plain value makes things even more complicated.
I assume that "things that broke" just means those, that have a key broke
:
select j.key
from the_table t
cross join lateral (
select *
from jsonb_each(t.the_column) as j(key, item)
where j.item ? 'broke'
) j;
To find those with a year > 1988
is tricky because of the two different ways of storing the year:
select j.key
from the_table t
cross join lateral (
select *
from jsonb_each(t.the_column) as j(key, item)
where case
when jsonb_typeof(j.item -> 'year') = 'array' then (j.item -> 'year' -> 0)::int
else (j.item ->> 'year')::int
end > 1988
) j;
When checking for the "engine" array item, you probably should also check if it's really an array:
select j.key
from the_table t
cross join lateral (
select *
from jsonb_each(t.the_column) as j(key, item)
where jsonb_typeof(j.item -> 'engine') = 'array'
and (j.item -> 'engine' ->> 1)::numeric > 50
) j;