JSON value of one JSON in JSON list value of other JSON

Question

With SQLalchemy on postgres, I want to know if one JSON is 'IN' another JSON, which should be in the ON clause of a JOIN, so something like:

SELECT json_1, ... FROM table_1
JOIN
SELECT json_2, ... FROM table_2
ON json_1['b'] IN json_2['b']

Where for example:

json_1 = {"a": ["123"], "b": ["456", "789"]}
json_2 = {"a": ["123"], "b": ["456"]}

So I want to know if json_2['b'] is in json_1['b'] in SQLAlchemy. Not sure of IN can even be used in the ON clause, but maybe a contains or anything else?

Answer 1

If the data type is jsonb, then I believe PostgreSQL supports contains queries to see if one JSON is a subset of another. As such, SQLAlchemy would support performing this type of SELECT. Check out the following links for more details:

• https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB.Comparator.contained_by
• Sqlalchemy json column - how to preform a contains query
• https://peterburroughs.com/journal/using-postgres-and-jsonb-with-python/

I would also like to point out that if the data type in PostgreSQL is json, you may be out of luck, as PostgreSQL does not appear to natively support comparing these objects to each other.

Barring that, you could potentially use Python convert the JSON values to strings and perform a regex-based comparison - but I'm worried that this approach fails, because in the following example, I would imagine you'd consider json_2 to still be a subset of json_1:

json_1 = {"a": ["123"], "b": ["789", "456"]}
json_2 = {"a": ["123"], "b": ["456"]}

And barring that, you may be in the territory of the subgraph isomorphism problem. networkx contains an implementation of the VF2 algorithm, which may help perform this comparison - but that approach may be treacherous.