How to query nested JSONB format data column in PostgreSQL?

Question

I have data gene expression data in jsosnb column in multiple rows for different samples as shown below:

Sample       Gexp
Sample A    {"data": [{"pval": 0.0154, "Protein": "A0A0B4J2D5", "FoldChange": 1.3534, "MinusLog10p": 0.1334, "Significance": "Non-significant"}, {"pval": 0.0689, "Protein": "A0FGR8", "FoldChange": 2.5448, "MinusLog10p": 1.1615, "Significance": "Significant"}]}
Sample B    {"data": [{"pval": 0.0824, "Protein": "A0A0B4J2D5", "FoldChange": -0.1676, "MinusLog10p": 0.1084, "Significance": "Non-significant"}, {"pval": 0.0219, "Protein": "A0FGR8", "FoldChange": 2.3448, "MinusLog10p": 1.1615, "Significance": "Significant"}]}

I need to query across the column containing multiple records where a certain protein has a pval or FoldChange in a certain range. I tried multiple solutions provided in this forum (Search in nested Postgresql JSONB column, Postgresql query for objects in nested JSONB field, Query simplified JSONB form JSONB column containing nested JSON from a Postgresql database?, How to query nested array with heterogeneous elements in PostgreSQL JSONB column, etc., with no luck. Can someone help me?

score 1 · Accepted Answer · answered Dec 26 '22 at 00:43

The conditions for selecting the data were not precisely (unambiguously) described in the question. Exemplary, when we are looking for the A0FGR8 protein in the pval range of 0.02 to 0.03, the query might look like this:

select sample, value
from my_table
cross join jsonb_array_elements(gexp->'data')
where value->>'Protein' = 'A0FGR8'
and (value->>'pval')::numeric between 0.02 and 0.03

Test the query in Db<>fiddle.

How to query nested JSONB format data column in PostgreSQL?

1 Answers1