Join tables using a value inside a JSONB column

Question

There are two tables:

Authorized contacts ( auth_contacts):

(
userid varchar
contacts jsonb
)

contacts contains an array of contacts with attributes {contact_id, type}

discussion:

(
contact_id varchar
discussion_id varchar
discussion_details jsonb
)

The table auth_contactscontains at least 100 thousand records, which makes it not such a JSONB type that does not fit, because it doubles or triple the number of records.

Sample data for auth_contacts:

userid  | contacts
'11111' | '{"contact": [{"type": "type_a", "contact_id": "1-A-12"}
                      , {"type": "type_b", "contact_id": "1-A-13"}]}'

discussion the table has 5 million odd entries.

I want to join discussion.contact_id(relational column) with the contact id, which is a json object inside json's array of objects auth_contacts.contacts.

One very crude way:

SELECT *
FROM discussion d 
JOIN (SELECT userid, JSONB_OBJECT_KEYS(a.contacts) AS auth_contact
      FROM auth_contacts a) AS contacts
      ON (d.contact_id = contacts.auth_contact::text)

, , runtime create (inner sql) userid vs contact id table ( , , , JSONB 26 + , . : PostgreSQL 9.4: / JSON

, , JOIN d.contact_id = contacts -> contact -> contact_id? , .

?

Answer 1

" " . , :

SELECT *
FROM  auth_contacts a
    , jsonb_to_recordset(a.contacts->'contact') AS c(contact_id text)
JOIN  discussion d USING (contact_id);

, , contains @>:

SELECT *
FROM   auth_contacts a
JOIN   discussion    d ON a.contacts->'contact'
                    @> json_build_array(
                         json_build_object('contact_id', d.contact_id)
                       )::jsonb

JSON, . , , jinb_path_ops GIN:

CREATE INDEX auth_contacts_contacts_gin_idx ON auth_contacts
USING  gin ((contacts->'contact') jsonb_path_ops);

:

• JSON
• Postgres 9.4 jsonb array

, . :

, JSONB, .

. , JSON. " " , , , Postgres. :

• JSONB Postgres 9.4
• " " PostgreSQL?