Two table join with sub-jsonb matching

Question

I have two tables in PostgreSQL, A & B, for simplicity with exactly the same structure:

{
    id varchar2(32) unique,
    data jsonb
}

Is it possible to write query, that will return all rows from A, for which particular B.data is "sub-json" of A.data?

Expressed in SQL alike terms:

SELECT A.*
FROM A, B
WHERE B.id = ? AND B.data is sub-json of A.data

json1 is sub-json of json2 means that by removing some key/value pairs from A you can get B. Example:

{
   "key1":"value1",
   "key3":"value3"
}

is sub-json of

{
   "key1":"value1",
   "key2":"value2",
   "key3":"value3"
}

json-s could have more complex structure.

If it is possible then how performant would it be compared to decomposing these json-s into rows and doing relational table joins?

Answer 1

The <@ operator is just what you mean by 'is sub-json of'.
I would also suggest to rather use the join ... on syntax than the obsolete one that uses the where clause.

select a.*
from a 
JOIN b ON b.data <@ a.data
where b.id = ?;

As for performance, you can create and use GIN indexes on JSONB fields that would make the above query very fast. The flatten-then-join approach would not give you tangible benefits.

Unrelated but better use lowercase names in PostgreSQL.