How can I find a outer (wildcard) key that contains certain parameters within a JSONB field in PostgreSQL?

lumera 0
Viewed 20

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:

  • Find outer key(s) that broke. ("Car23" and "Unique")
  • find outer key(s) that have a year above 1988. ("Banana")
  • Find outer key(s) that have engine info and the second array number is higher then 50. ("asdf")

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.

jsonb postgresql
1 Answers

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;
a_horse_with_no_name0
Related