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

you could make life a lot easier by storing {'apps_created': '3'} ie drop the 'field' and 'value' from each dictionary so that instead of a dictionary with two elements you have a dictionary with one. In fact if you did this you might be able to chuck the whole array of dictionaries thing and have a large dictionary which would be so much easier to work with in postgresql and in your code — e4c5
– e4c5, Commented May 21, 2016 at 18:49
The reason for storing it that way is because I let users submit custom data fields/values and hence cannot know them at design time. AFAIK this is not possible when hardcoding the key like that — Tarlen
– Tarlen, Commented May 21, 2016 at 19:27
With my suggestion you still don't need to know what those fields are at design time. — e4c5
– e4c5, Commented May 22, 2016 at 0:46

Abelisto · Accepted Answer · 2016-05-22 11:44:23Z

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));

edited May 22, 2016 at 11:44

answered May 21, 2016 at 21:22

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tarlen Over a year ago

Awesome, thanks. Can you explain the t(x) syntax? I figure that it maps x to each element of the array, but a brief elaboration would be much appreciated

Tarlen Over a year ago

Also, with regards to indexing this query, is a GIN index on field and then value the way to go?

Abelisto Over a year ago

@Tarlen 1) It is standard syntax for subqueries in the from clause, where t is an alias for subquery and optional aliases for fields in brackets: ... from (select 1 as x) t or ... from (select 1) t(x). In our case unnest (custom) is equal to (select unnest(custom)); 2) I'v never used GIN indexes.

Collectives™ on Stack Overflow

How to query a JSONB array?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related