* Extract the attributes you're interested in into their own columns, index these. With the extraction happening outside the database, this is the most flexible option.
* Similar to above, use a trigger to automatically extract these attributes.
* Also similar to above, used a generated column[0] to automatically extract these attributes.
* Create an index on the expression[1] you use to extract the attributes.
My use a JSON in PostgreSQL tends towards the first option. This works well enough for cases where documents are ingested and queried, but not updated. The last three options are automatic - add/change the JSON document and the extracted/indexed values are automatically updated.
* Extract the attributes you're interested in into their own columns, index these. With the extraction happening outside the database, this is the most flexible option.
* Similar to above, use a trigger to automatically extract these attributes.
* Also similar to above, used a generated column[0] to automatically extract these attributes.
* Create an index on the expression[1] you use to extract the attributes.
My use a JSON in PostgreSQL tends towards the first option. This works well enough for cases where documents are ingested and queried, but not updated. The last three options are automatic - add/change the JSON document and the extracted/indexed values are automatically updated.
[0] https://www.postgresql.org/docs/12/ddl-generated-columns.htm...
[1] https://www.postgresql.org/docs/12/indexes-expressional.html