How to query a JSONB array?

Question

I have a table

Visitor: (id, .., custom::jsonb[])

custom is an array of JSON objects of the form {field:string, value:string}. Example:

{"field": "apps_created", "value": "3"}

Say I want to find all the Visitors with 3 or more apps_created, how would I go about this? Note: every Visitor can have different fields and often there is not overlap with other visitors.

I've tried to consult the postgres documentation or other stackoverflow questions, but I have a hard time figuring out what functions/operators are used in this situation.

Any help is much appreciated

Answer 1

select *
from visitor
where
  exists (
    select 1 
    from unnest(custom) t(x) 
    where x->>'field' = 'apps_created' and (x->>'value')::int >= 3);

---

Upd
However the classic way to implement such things in relational databases is (schematic):

create table entity (
  entity_id serial not null primary key,
  ...);

create table param (
  param_id serial not null primary key,
  param_name varchar not null unique,
  ...);

create table param_value (
  param_id int references param(param_id),
  entity_id int references entity(entity_id) on delete cascade,
  value varchar,
  ...
  primary key (param_id, entity_id));