current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

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

  1. An object, where each key is the contact type such as mailing, billing, emergency etc. These are loosely defined.
  2. The value for each key in #1 is an array containing 1 or more contacts for each type
  3. 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}'

share|improve this question
up vote 2 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.