Postgres compare multiple jsonb fields

Question

I have a jsonb "contacts" field in a table which is:

An object, where each key is the contact type such as mailing, billing, emergency etc. These are loosely defined.
The value for each key in #1 is an array containing 1 or more contacts for each type
Each value inside #2 is an object containing loosely defined fields.

Example:

  {
  "main": [{
    "name": "Bobby Smith",
    "email": "[email protected]", 
    "telephone": "555-999-0000"
  }, {
    "name": "Joan Smith",
    "email": "[email protected]", 
    "telephone": "555-999-0002"
  }],
  "billing": [{
    "name": null,
    "region": "Arizona",
    "address": ["PO BOX 123", "456 Nowhere Road"],
    "country": "USA",
    "locality": "BigMac",
    "postalCode": "90210"
  }],
  "emergency": [{
    "name": "John Cooper",
    "email": "[email protected]",
    "telephone": "555-987-0000"
  }]
}

I'd like to have a simple way to do a comparison on say the name fields that is agnostic to the keys in the outermost object, as well as the number of elements in each array.

Effectively this: SELECT * FROM clients WHERE contacts#>>'{*, *, name}' = 'John Cooper';

Would return the row containing the above as it matches '{emergency, 0, name}'

pozs · Accepted Answer · 2016-05-06 09:35:28Z

2

You need to unwind data for your current database design, like:

select t.*
from   t, jsonb_each(contacts) e, jsonb_array_elements(e.value) c
where  c ->> 'name' = 'John Cooper'

But this can't use any index for your query.

A better design would be to only store contact data on each row in a joining table, like:

  t                  t_contact
------------------  -------------------------
 t_id primary key    t_id references t(t_id)
                     contact_type text
                     contact jsonb

Then, you could index t_contact(contact) and query, like:

select   t.*
from     t
join     t_contact using (t_id)
where    contact ->> 'name' = 'John Cooper'
group by t.t_id

edited May 6, 2016 at 9:35

answered May 6, 2016 at 9:30

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

Postgres compare multiple jsonb fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related